How do I parse a string in Oracle?

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.

You can use regular expressions to find this:

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

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).

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))

Below is my tested variations from cwallenpoole and Craig. For the regexp - note that if "request=" does not exist, the result will be the entire string. user349433 was partly there too, a space before "request=" in the search works just as well:

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;
/

Please note the equal sign "=" does not necessarily have to come immediately after the request variable in the assignment. As such, it is not entirely correct to search for "request=". You should create a basic finite state machine using INSTR to first find "request", then find "=", ...