How do I parse a string in Oracle?

Question

How can I parse the value of "request" in the following string in Oracle?

<!-- accountId="123" activity="add" request="add user" -->

The size and the position of the request is random.

Solution 1

You can use regular expressions to find this:

regexp_replace(str, '.*request="([^"]*)".*', '\1')

Solution 2

Use INSTR(givenstring, stringchartosearch,start_position) to find the position of 'request="' and to find the position of the closing '"'.

Then use substr(string, starting_position, length).

Solution 3

You'd use a combination of instr and substr

THIS EXAMPLE IS FOR EXAMPLE PURPOSES ONLY. DO NOT USE IT IN PRODUCTION CODE AS IT IS NOT VERY CLEAN.

substr(my_str, 
       -- find request=" then get index of next char.
       instr(my_str, 'request="') + 9, 
       -- This is the second " after request. It does not allow for escapes
       instr(substr(my_str,instr(my_str, 'request="')), 2))

Comments

[Josh]

You might want to consider starting the regex with .*\s+request so that it doesn't match things like subrequest="foo".