Skip to content
Advertisement

Oracle regex replace multiple occurrences of a string surrounded by commas

I am looking for a way to replace (remove / replace with ”) a character string in a comma-separated list of values in a column in an Oracle SQL database. For example, suppose I have the following data:

I am looking to replace all occurrences of the substring ‘SL’, strictly (i.e. not including ‘OSL’), with an empty string, ''. The ideal result would look like this:

I have tried to use the regexp_replace function, but it only eliminates every other occurence, i.e.

I have achieved my goal successfully in other regex implementations that have the word boundary b construct available, but have not found a solution for Oracle’s regex.

UPDATE

  1. Version: We are on Oracle version 11g.
  2. Addition example case PI,SL,SLR,PK
  3. Additional example cases PK,SL, SL,SL, SL, PK

Advertisement

Answer

because Oracle’s regex moves the matching position forward after a match you need to do regexp double times unfortunately

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement