22208

Oracle regex replace multiple occurrences of a string surrounded by commas

<h3>Question</h3>

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:

select ('SL,PK') as col1 from dual union all select ('PK,SL') as col1 from dual union all select ('SL,SL') as col1 from dual union all select ('SL') as col1 from dual union all select ('PK') as col1 from dual union all select ('PI,SL,PK') as col1 from dual union all select ('PI,SL,SL,PK') as col1 from dual union all select ('PI,SL,SL,SL,PK') as col1 from dual union all select ('PI,SL,SL,SL,SL,PK') as col1 from dual union all select ('PI,OSL,SL,PK') as col1 from dual union all select ('PI,SL,SLR,PK') as col1 from dual COL1 ----- SL,PK PK,SL SL,SL SL PK PI,SL,PK PI,SL,SL,PK PI,SL,SL,SL,PK PI,SL,SL,SL,SL,PK PI,OSL,SL,PK PI,SL,SLR,PK

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:

COL2 ----- ,PK PK, , (null) PK PI,,PK PI,,,PK PI,,,,PK PI,,,,,PK PI,OSL,,PK PI,,SLR,PK

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

SELECT col1, regexp_replace(col1,'(^|,)(SL)($|,)','\1' || '' || '\3',1,0,'imn') as col2 FROM ( SELECT ('SL,PK') as col1 FROM dual UNION ALL SELECT ('PK,SL') as col1 FROM dual UNION ALL SELECT ('SL,SL') as col1 FROM dual UNION ALL SELECT ('SL') as col1 FROM dual UNION ALL SELECT ('PK') as col1 FROM dual UNION ALL SELECT ('PI,SL,PK') as col1 FROM dual UNION ALL SELECT ('PI,SL,SL,PK') as col1 FROM dual UNION ALL SELECT ('PI,SL,SL,SL,PK') as col1 FROM dual UNION ALL SELECT ('PI,SL,SL,SL,SL,PK') as col1 FROM dual UNION ALL SELECT ('PI,OSL,SL,PK') as col1 FROM dual UNION ALL SELECT ('PI,SL,SLR,PK') as col1 FROM dual ) COL1 COL2 ----- ----- SL,PK ,PK PK,SL PK, SL,SL ,SL SL (null) PK PK PI,SL,PK PI,,PK PI,SL,SL,PK PI,,SL,PK PI,SL,SL,SL,PK PI,,SL,,PK PI,SL,SL,SL,SL,PK PI,,SL,,SL,PK PI,OSL,SL,PK PI,OSL,,PK PI,SL,SLR,PK PI,,SLR,PK

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

<ol><li>Version: We are on Oracle version 11g.</li> <li>Addition example case PI,SL,SLR,PK</li> <li>Additional example cases PK,SL, SL,SL, SL, PK</li> </ol>
<h3>Answer1:</h3>

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

regexp_replace(regexp_replace(col1,'(^|,)(SL)(\W|$)','\1\3',1,0,'imn') ,'(^|,)(SL)(\W|$)','\1\3',1,0,'imn')
<h3>Answer2:</h3>

If you can choose a special character that doesn't appear in the string - I'm using semicolon (;) you may follow this steps

1) double each comma to comma + semicolon

2) perform your regexp only adding the semicolon as a possible delimiter to the pattern:

'(^|,|;)(SL)($|,|;)

3) remove all semicolons

The complete query

SELECT replace(regexp_replace(replace(col1,',',',;'),'(^|,|;)(SL)($|,|;)','\1\3',1,0,'imn'),';') as col2 FROM tab;

produce the expected result

,PK PK, , (null) PK PI,,PK PI,,,PK PI,,,,PK PI,,,,,PK PI,OSL,,PK PI,,SLR,PK

来源:https://stackoverflow.com/questions/61940076/oracle-regex-replace-multiple-occurrences-of-a-string-surrounded-by-commas

Recommend

  • How to load Javascript file in a Service Worker dynamically?
  • Object differ hasChanges where no changes should be detected
  • How can I improve CGContextFillRect and CGContextDrawImage performance
  • set device time programmatically in android [duplicate]
  • ivars in XCode templates prefixed with one or two underscores
  • Persisting meta data on item, and retrieving at checkout complete
  • How to flatten IO (IO ())?
  • Spring Batch process an encoded zipped file
  • Python argparse AssertionError when number of arguments exceeds threshold
  • SQLALCHEMY_DATABASE_URI not set
  • how to get Eclipse Workspace Location URI
  • Where does .net core search for certificates on linux platform
  • ClickOnce is displaying the '.application' file as XML
  • WebGL - Rotating object in the direction it is travelling
  • Nested information in Firebase
  • Iteratively insert rows into a postgreSQL database using SQLAlchemy in python
  • WPF/MVVM: Re-use a ViewModel in multiple Controller and Separation of Concerns
  • How to open my facebook page in facebook app from button click of an android app [duplicate]
  • Can testing with real adMob ads after the app's development results in adMob's policy viol
  • Bluetooth Low Energy device scanning Failed with an exception
  • iOS MDM Enrollment SCEP Specification and PKIOperation and operation=PKIOperation &message=MMIC
  • Signature method in Inspect module for python 2
  • Display standard razor/mvc 3 validation messages displayed in another language
  • Special characters (ë) in JSON-response
  • How to delete first 7 characters of folder name by using batch script?
  • Python sum values in tuple in a list in a dictionary?
  • Calling a flash ExternalInterface in swiffyobject
  • SyntaxError: expected expression, got '.'
  • $this->a->b->c->d calling methods from a superclass in php
  • How to create OLE Automation to be used with ClearExplorer
  • Delete a ListItem by clicking the ImageView within the ListItem
  • Shiny - change the size (padding?) of dropdown menu (select tags) smaller
  • Unable to create Access token grant type in wso2 API manager store to test API