CODEWITHSUNDEEP
regexoracle-database
Superdooperhero
Superdooperhero

Reputation: 8096

Oracle regexp_substr to retrieve name from where clause in string

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?

Upvotes: 1

Views: 177

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

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

Upvotes: 0

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627044

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 '

Upvotes: 2

Related Questions