CODEWITHSUNDEEP
sqlregexoracle-database
Varghese
Varghese

Reputation: 47

Fetch second word in a string until '(' or ' ' encountered

I want to fetch the second word in a string like in the code below, I'm trying to get the function's name unstring. But I need a regex that fetches me the second word until '(' or a space is encountered. 

SELECT REGEXP_SUBSTR('FUNCTION unstring(i_string    VARCHAR2', '(\S*)(\S)', 1, 2) 
FROM DUAL;

With this regex, I get unstring(i_string as the result. I'm trying to fetch the function's name so it's not necessary that a function have arguments, hence the '(' or ' ' encountered condition.

Upvotes: 2

Views: 149

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627507

Use \w+ to match a single word and \s+ to match whitespace between the two words. Capture the second one:

select regexp_substr('FUNCTION unstring(i_string    VARCHAR2','^\w+\s+(\w+)', 1, 1, NULL, 1) from dual

The pattern means:

  • ^ - start of string anchor
  • \w+ - 1 or more letters/digits/_
  • \s+ - 1+ whitespaces
  • (\w+) - Capturing group 1 matching 1+ word chars (letters/digits/_)

The last 1 argument tells regexp_substr to return the contents of the first capturing group.

Upvotes: 2

Related Questions