Geek Answers Handbook

Return all regex matches in Oracle

I have a table containing a VARCHAR2 column named COMMANDS.

The data in this column is a bunch of hard-to-read ZPL code that will be sent to the printer for labels, and among the ZPL there are several tokens in the form {TABLE.COLUMN}.

I would like a good list of all the various {TABLE.COLUMN} tokens that are in COMMANDS. I wrote the following regular expression to match the token format:

SELECT REGEXP_SUBSTR(COMMANDS,'\{\w+\.\w+\}') FROM MYTABLE;

The regex works, but returns only the first matched token in the string. Is there a way to return all regular expression matches for each line?

I am using Oracle 11GR2.

Edit - here is a small example of data from one row - each row has many such rows:

 ^FO360,065^AEN,25,10^FD{CUSTOMERS.CUST_NAME}^FS ^FO360,095^AAN,15,12^FD{CUSTOMERS.CUST_ADDR1}^FS

So, if this is the only row in the table, I would like to return:

 {CUSTOMERS.CUST_NAME} {CUSTOMERS.CUST_ADDR1}
+4
source share
2 answers

You provided a sample of data saying that it is one line, but presented it as two different lines. So this example is based on your words.

  -- Sample of data from your question + extra row for the sake of demonstration -- id column is added to distinguish the rows(I assume you have one) with t1(id, col) as( select 1, '^FO360,065^AEN,25,10^FD{CUSTOMERS1.CUST_NAME}^FS^FO360,095^AAN,15,12^FD{CUSTOMERS1.CUST_ADDR1}^FS' from dual union all select 2, '^FO360,065^AEN,25,10^FD{CUSTOMERS2.CUST_NAME}^FS^FO360,095^AAN,15,12^FD{CUSTOMERS2.CUST_ADDR2}^FS' from dual ), cnt(c) as( select level from (select max(regexp_count(col, '{\w+.\w+}')) as o_c from t1 ) z connect by level <= z.o_c ) select t1.id, listagg(regexp_substr(t1.col, '{\w+.\w+}', 1, cnt.c)) within group(order by t1.id) res from t1 cross join cnt group by t1.id

Result:

  ID RES --------------------------------------------------------- 1 {CUSTOMERS1.CUST_ADDR1}{CUSTOMERS1.CUST_NAME} 2 {CUSTOMERS2.CUST_ADDR2}{CUSTOMERS2.CUST_NAME}

According to @a_horse_with_no_name, commenting on a question is indeed much simpler than just replacing everything else that doesn't match the pattern. Here is an example:

  with t1(col) as( select '^FO360,065^AEN,25,10^FD{CUSTOMERS.CUST_NAME}^FS^FO360,095^AAN,15,12^FD{CUSTOMERS.CUST_ADDR1}^FS' from dual ) select regexp_replace(t1.col, '({\w+.\w+})|.', '\1') res from t1

Result:

 RES ------------------------------------------- {CUSTOMERS.CUST_NAME}{CUSTOMERS.CUST_ADDR1}
+5
source

I think not. You must write PL / SQL so that others match tokens. My best advice for you is to use a pipelined function.

First create a type:

 create type strings as table of varchar2(200);

Then the function:

 CREATE OR REPLACE function let_me_show return strings PIPELINED as l_n number; l_r varchar2(200); begin for r_rec in ( SELECT commands FROM MYTABLE ) loop l_n := 1; l_r := REGEXP_SUBSTR(r_rec.COMMANDS,'\{\w+\.\w+\}', 1, l_n); while l_r is not null loop pipe row(l_r); l_n := l_n + 1; l_r := REGEXP_SUBSTR(r_rec.COMMANDS,'\{\w+\.\w+\}', 1, l_n); end loop; end loop; end;

Now you can use the function to return the results:

 select * from table(let_me_show())
+3
source

More articles:


All Articles