How to use regex expression in oracle sql query?

Question

In my location field I have :

"Location:
SOME PLACE

Additional Details:
DEFECT
"

Somehow I need this to take out the "SOME PLACE". I have tried to understand how the regexp_substr() function works, but it is very different from my C# background and I keep getting weird results like nulls.

Normally I would have expected to do something like :

regexp_substr(LocationField, 'Location:(.*)\n') as "NewLocation"

And for it to cut out the part in parenthesis... but I was wrong. Any ideas?

SOLUTION :

regexp_replace(FIELD, 'Location:'||CHR(13)||CHR(10)||'(.*).*$'||CHR(13)||CHR(10)||CHR(13)||CHR(10)||'Additional Details:.*$', '\1', 1, 1, 'n' ) as "Location"

DazzaL · Accepted Answer · 2013-02-11 16:55:36Z

1

you can do it like this:

regexp_replace(LocationField, 
               'location:'||chr(10)||'([^'||chr(10)||']*).*$', '\1', 
                1, 1, 'n' )

ie the regex string

location:\n([^\n]*).*$

but as oracle will not work with \n we splice in chr(10) in its place.

the n mode means that . will match the newline character (so .*$ gets rid of everything after the newline following the SOME TEXT.

if location can have mulitple lines of text followed by Addition Details, you could use:

regexp_replace(LocationField, 
               'location:'||chr(10)||'(.*)'||chr(10)||'Additional.*$',  
               '\1', 1, 1, 'n' ) as "NewLocation"

answered Feb 11, 2013 at 16:55

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

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

1 Comment

sprocket12 Over a year ago

Thanks for your answer it helped me most in reaching the solution.

DCookie · Accepted Answer · 2013-02-11 14:41:26Z

0

Use REGEXP_REPLACE instead:

select REGEXP_REPLACE( target, LocationField, 'Location:(.*)\n', '' ) as "NewLocation"
from your_table;

Should get you going in the right direction.

edited Feb 11, 2013 at 14:41

answered Feb 11, 2013 at 14:31

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

1 Comment

sprocket12 Over a year ago

Hmm... you seem to have 4 parameters, and the link has 3? I tried with 3 omitting the target, because the LocationField is the target, and it returned to me the whole field...

APC · Accepted Answer · 2013-02-11 15:05:04Z

0

You need to run the regex in multi-line mode, which means populating the full signature of the REGEXP_SUBSTR() call:

select regexp_substr( locationField, '^([A-Z ]+)$', 1, 1, 'm')
from your_table
/

The 'm' is a match parameter specifying multi-line mode. This means ^ and $ in the matching pattern are treated as newline boundaries rather than the start and end of the string. Find out more.

answered Feb 11, 2013 at 15:05

APC

147k20 gold badges180 silver badges289 bronze badges

Collectives™ on Stack Overflow

How to use regex expression in oracle sql query?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related