A good friend asked me how to replace the last full stop (period) in a field with another character using a regular expression e.g. a comma. When I asked “what language?” it turned out he wanted to do this using REGEXP_REPLACE in Oracle SQL.
It’s a problem with a couple of solutions that I could immediately think of – one more “Oracle”, the other more “regular expression”.
First the Oracle solution:
REGEXP_REPLACE({FIELD},'\.',',',INSTR({FIELD},'.',-1)) |
This uses backward search capability of INSTR() to find position of the last ‘.’ in the field and return it so that the REGEXP_REPLACE only starts the replace from that point on-wards.
Now a “pure” regular expression version:
REGEXP_REPLACE({FIELD},'(\.)([^.]*$)',',\2') |
This one is a bit more involved as it uses grouping and a negated character class ([^.] = “not a period”) as well as a back reference (\2).