Extract a part of a string using regexp_substr

Question

I am running the following in Oracle sql. I am able to obtain the result I want but would like to understand how to apply the alternative. I want to pull out the name which always starts at the 18th character, but the names and characters after are always different.

Example: "Assigned to user John Smith in group 5678"

This is found in table history.description.

If I use SUBSTR (HISTORY.DESCRIPTION, 18, INSTR (HISTORY.DESCRIPTION,' in')-18 AS NAME

I get my desired result of "John Smith"

I am trying SUBSTR (HISTORY.DESCRIPTION,18, REGEXP_SUBSTR (HISTORY.DESCRIPTION, '(\S*)(\w)', ' in')

Ora 01722 invalid number error is what I get.

I am not understanding how to apply this function

Please read the documentation carefully, https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm it says that the third parameter named **position** must be a positive or negative **number**, while you are passing a string `in`. — krokodilko, Mar 03 '16 at 20:39

Gary_W · Answer 1 · 2016-03-04T16:42:01.123

Can you assume it is safe to grab all text between the first "user" and the last "in"? Will that text be constant?

SQL> with history(description) as (
      select 'Assigned to user John Smith in group 5678' from dual
    )
    select regexp_substr(description, 'user\s(.*)\sin', 1, 1, 'i', 1) name
    from history;

NAME
----------
John Smith

SQL>

Or to really tighten up the regex to match more of the string, you could change it to start at the 18th character and match all characters up to but not including the space at the start of the "in group " text:

'^.{17}(.+) in group \d+$'

Extract a part of a string using regexp_substr

1 Answers1