CODEWITHSUNDEEP
stringoracle-databasefunctionoracle11gregexp-substr
hadoo
hadoo

Reputation: 213

Extract a part of a string using regexp_substr

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

Upvotes: 0

Views: 1163

Answers (1)

Gary_W
Gary_W

Reputation: 10360

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

Upvotes: 1

Related Questions