Oracle regexp_substr to retrieve name from where clause in string

Question

I am trying to parse a where clause in a string to return the name of the lookup_type. The where clause could be anything.

This is what I currently have:

select regexp_substr('where lookup_type = ''THE MILK'' ', 'lookup_type(\s*)=(\s*)''(^''*)''')
from dual

For some reason this does not work. Also how can I only return THE MILK from the regex?

Wiktor Stribiżew · Accepted Answer · 2018-01-10 15:28:04Z

2

You may use

select regexp_substr('where lookup_type = ''THE MILK'' ', 'lookup_type\s*=\s*''([^'']+)''', 1, 1, NULL, 1) as result from dual

See the online demo

Details

lookup_type - matches a literal substring
\s*=\s* - matches = enclosed with 0+ whitespaces
'' - matches a '
([^'']+) - matches and captures 1 or more chars other than '
'' - matches a '

answered Jan 10, 2018 at 15:28

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

1 Comment

Thorsten Kettner Over a year ago

Correct, ^ means "start of string"; [^ means "not one of the characters in brackets". As we don't need to capture anything, we can even replace ([^'']+) by [^'']+.

Kaushik Nayak · Accepted Answer · 2018-01-10 15:36:50Z

0

You could escape some quotes with q notation( alternative quoting mechanism)

SELECT REGEXP_SUBSTR(q'#where lookup_type = 'THE MILK'#',
              q'#lookup_type\s*=\s*'([^']+)#', 1, 1, 'i', 1) name
FROM   dual  ;

DEMO

answered Jan 10, 2018 at 15:36

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Oracle regexp_substr to retrieve name from where clause in string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related