regexoracle-databaseoracle19cregexp-substrregexp-like

extract values after comma's using regexp

Am using Oracle 19c database

Below is my string value

variable B1 varchar2(60)
exec :B1:='(199,''TEST121''),(156,''TEST''),(1561,''TEST99'')';

I want the output as

|    ID    |     NAME       |
| -------- | -------------- |
|       199|   TEST121      |
|       156|   TEST         |
|      1561|   TEST99       |

select  regexp_substr(regexp_substr(:b1,'[^A-Z+0-9][0-9]+', 1,level),'[0-9]+') as id , regexp_substr(:b1,'[A-Z]+[0-9]', 1,level) as name from dual connect by regexp_substr(:b1,'[0-9]', 1,level) is not null;

This query is only giving output for string values ending with digits.

Solution

  • Here's one option:

    SQL> WITH
  2     test (col)
  3     AS
  4        (SELECT '(199,''TEST121''),(156,''TEST''),(1561,''TEST99'')' FROM DUAL)
  5  SELECT SUBSTR (str, 1, INSTR (str, ',') - 1) id,
  6         SUBSTR (str, INSTR (str, ',') + 1) name
  7    FROM (    SELECT REGEXP_SUBSTR (
  8                        REPLACE (
  9                           REPLACE (
 10                              REPLACE (REPLACE (col, '),(', '#'), CHR (39), ''),
 11                              '(',
 12                              ''),
 13                           ')',
 14                           ''),
 15                        '[^#]+',
 16                        1,
 17                        LEVEL) str
 18                FROM test
 19          CONNECT BY LEVEL <= REGEXP_COUNT (REPLACE (col, '),(', '#'), '#') + 1);

ID                               NAME
-------------------------------- --------------------------------
199                              TEST121
156                              TEST
1561                             TEST99

SQL>

    What does it do?