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

Question

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.

Gordon Linoff · Accepted Answer · 2020-05-21 14:01:55Z

1

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.

answered May 21, 2020 at 14:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4888203 Over a year ago

There may be multiple instances - I could kludge some horrible SQL with INSTR() and SUBSTR() to cater for a single instance.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related