Tags

, , , , ,

This is supposed to be one of the most common question among all the Oracle discussion forums, and especially folks looking for REGULAR EXPRESSION over web, would have definitely come across such a question. I find such questions at least once a day on OraFAQ, stack overflow, OTN forums…

So, let me give a small demonstration about how to find all the occurrences of specific/similar word in a string. As an example, I will take a huge string literal which itself is a complex select query having multiple join clause. So, the task is to find all the table names. in this case, the driving factor is that the word will always have initial characters as ‘table’, followed by digits or characters.

The string :


SELECT b.col1, 
       a.col2, 
       Lower(a.col3) 
FROM   table1 a 
       inner join table2 b 
               ON a.col = b.col 
                  AND a.col = b.col 
       inner join (SELECT col1, 
                          col2, 
                          col3, 
                          col4 
                   FROM   tablename) c 
               ON a.col1 = b.col2 
WHERE  a.col = 'value' 

To find :

table1, table2 , tablename

Let’s see how :


SQL> WITH DATA AS
  2    (SELECT q'[select b.col1,a.col2,lower(a.col3) from table1 a inner join table2 b on
  3  a.col = b.col and a.col = b.col inner join (select col1, col2, col3,col4 from tablename )
  4  c on a.col1=b.col2 where a.col = 'value']' str
  5    FROM DUAL
  6    )
  7  SELECT LISTAGG(TABLE_NAMES, ' , ') WITHIN GROUP (
  8  ORDER BY val) table_names
  9  FROM
 10    (SELECT 1 val,
 11      regexp_substr(str,'table[[:alnum:]]+',1,level) table_names
 12    FROM DATA
 13      CONNECT BY level <= regexp_count(str,'table')
 14    )
 15  /

TABLE_NAMES
--------------------------------------------------------------------------------
table1 , table2 , tablename

Let me explain in detail, how that query works :

  • The REGEXP_SUBSTR looks for the words ‘table’, it could be followed by a number or string like 1,2, name etc.
  • To find all such words, I used connect by level technique, but it gives the output in different rows.
  • Finally, to put them in a single row as comma separated values, I used LISTAGG.
  • Oh yes, and that q'[]’ is the string literal technique.
  • Hope it helps!