CODEWITHSUNDEEP
sqloracle-database
Mark Marina
Mark Marina

Reputation: 733

Remove multiple values from a field with comma delimited values - SQL

I have to make some changes to a specific field (Oracle DB)

I would like to know what is the best way to remove multiple values from a field with comma delimited values (string) ? Example: Before: TYP,CRT,REW,PBR,ORT Remove TYP, CRT and ORT After: REW,PBR

Is using a nested REPLACE the only option ?

Upvotes: 0

Views: 1385

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26333

You can use REGEXP_REPLACE:

UPDATE myTable
SET myColumn =
  TRIM(TRAILING ',' FROM REGEXP_REPLACE(myColumn, '(TYP|CRT|ORT)(,|$)'))

The regex looks for TYP, CRT, or ORT followed by a comma or the end of the string. If it gets the very last value (for example the ORT in REW,ORT) it will leave a trailing comma. Rather than overcomplicate the regex, this example removes any trailing commas using the TRIM() function.

There's a SQLFiddle here.

Finally, Frank Schmitt's comment above is spot on - a comma-separated list like this in a column often means poor design. If you can split these values into a related details table you'll probably make things a lot easier.

Upvotes: 1

Related Questions