How to parse a string in Oracle?

Question

How can I parse the query value in the next line in Oracle?

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

The size and position of the request are random.

Answer 1

You can use regular expressions to find this:

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

Answer 2

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

Then use substr(string, starting_position, length) .

Answer 3

You used a combination of instr and substr

THIS EXAMPLE IS ONLY FOR EXAMPLES OF GOALS. DO NOT USE IT IN THE PRODUCT 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)) 

Answer 4

Below are my proven options from cwallenpoole and Craig. For regexp - note that if "request =" does not exist, the result will be a whole line. user349433 was also partially there, and the space before searching for "request =" also works:

 SET serveroutput ON DECLARE l_string VARCHAR2(100) := '<!-- accountId="123" activity="add" request="add user" -->'; l_result_from_substr VARCHAR2(50); l_result_from_regexp VARCHAR2(50); BEGIN SELECT SUBSTR(l_string, instr(l_string, 'request="') + 9, instr(SUBSTR(l_string,instr(l_string, 'request="')), '"', 2)-1), regexp_replace(l_string, '.* request="([^"]*)".*', '\1') INTO l_result_from_substr, l_result_from_regexp FROM dual; dbms_output.put_line('Result from substr: '||l_result_from_substr); dbms_output.put_line('Result from regexp: '||l_result_from_regexp); END; / 

Answer 5

Note that the equal sign "=" does not have to appear immediately after the request variable in the assignment. Thus, it is not practical to look for "request =". You must create the underlying state machine using INSTR in order to first find the "query", then find "=", ...