Opinionated Software

… we have opinions about everything!

REGEXP Problem —

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).

 


Categorised as: Hints and Tips | One-liners | Oracle | SQL



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.