How can I extract a word from a string using Oracle regexp_substr?

user_6420109 0
Viewed 57

I'm trying to extract a word from a string using Oracle 12c regexp_substr but no luck in understanding how it works, too much info in the net and I get confused.

So I want to extract tmp* tables from a string:

query_str:

 select 
 column1 c1,
 column2 c2
 from tmp_123 foo1, -- some comments here
 TAB1_123 TAB1
 where 1=1
;

Trying to use this but no "luck":

select regexp_substr(query_str, 'TMP_[A-z]+', 1, 1, 'i');

I want to extract until the space and the tmp table name can have numbers in the middle like this: tmp_123.

Any suggestion?

substr oracle regex
2 Answers

You can use either of the two:

select regexp_substr(query_str, 'TMP_\w+', 1, 1, 'i');
select regexp_substr(query_str, 'TMP_\S+', 1, 1, 'i');

The \w+ will match alphanumeric or underscore chars after TMP_ and \S+ will match one or more non-whitespace chars.

See the \w regex demo and the \S regex demo.

user_38329700

The major problem is that the SELECT statement shown is not valid in Oracle, where a FROM clause is required. Here's an example of how to make this work:

WITH cteData
  AS (SELECT 'select' AS QUERY_STR FROM DUAL UNION ALL
      SELECT 'column1 c1,' AS QUERY_STR FROM DUAL UNION ALL
      SELECT 'column2 c2' AS QUERY_STR FROM DUAL UNION ALL
      SELECT 'from tmp_123 foo1, -- some comments here' AS QUERY_STR FROM DUAL UNION ALL
      SELECT 'TAB1_123 TAB1' AS QUERY_STR FROM DUAL UNION ALL
      SELECT 'where 1=1' AS QUERY_STR FROM DUAL UNION ALL
      SELECT ';' AS QUERY_STR FROM DUAL)
select regexp_substr(query_str, 'TMP_[A-z]+', 1, 1, 'i') AS MATCH
  FROM cteData
  WHERE regexp_substr(query_str, 'TMP_[A-z]+', 1, 1, 'i') IS NOT NULL

Here I've put your data line-for-line into a Common Table Expression (CTE) named "cteData" which the SELECT then uses as the source of its data. This returns the line

tmp_123 foo1, -- some comments here

db<>fiddle here

user_2131360
Related