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, 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
– krokodilko, Commented Mar 3, 2016 at 20:39

Gary_W · Accepted Answer · 2016-03-04 16:42:01Z

1

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+$'

edited Mar 4, 2016 at 16:42

answered Mar 4, 2016 at 16:27

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract a part of a string using regexp_substr

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related