CODEWITHSUNDEEP
sqlregexoracle-database
user4888203
user4888203

Reputation: 1

Oracle SQL regex: extract every instance of a string and preceding/following characters

I'm pulling data from an Oracle CLOB field containing tens of thousands of characters. The data look like this:

...
196|9900000296567|V|
197|S05S53499|D|
198|TO|20170128000000|50118.0|||T|N|
196|9900009777884|V|
197|H02FC07599|D|
198|01|20170128000000|64452.0|||T|N|
198|02|20170128000000|14235.0|||T|N|
196|9900014386487|V|
197|S10C20599|D|
198|1|20170128000000|6246.0|||T|N|
196|9900015184256|V|
197|S13G44199|D|
198|L|20170128000000|1731.0|||T|N|
198|N|20170128000000|5915.0|||T|N|
196|9900018826270|V|
197|S10C20599|D|
198|01|20170128000000|3678.0|||T|N|
198|02|20170128000000|25286.0|||T|N|
...

I want to extract every occurrence of a string (e.g. S10C20599) with the preceding 25 characters and following 75 characters. If this bit is not possible I'd happily settle for the same number of preceding and following characters. I don't care if I get overlaps in the extracted data, and the code should not error if the search string occurs <25 characters from the beginning of the file or <75 characters from the end. Thanks for any tips.

Upvotes: 0

Views: 140

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If there is only one value, you can use:

select regexp_substr(col, '.{0-25}S10C20599.{0-75}')

Otherwise, you need to some sort of recursive or hierarchical query to fetch multiple values from a single string.

Upvotes: 1

Related Questions