Oracle SQL: Regular expression - retrive from a row specific characters

Question

I have this column RULE that has more than 300 rows (like below) and I need to use one of the regular expression in a select statement in order to retrieve (in different columns) the following:
MisTop50RankingTop50CCP & MisScLeDivProfit as column X
TUKB901 as column Y

PA_T50_TOP50 & PA_SC_LE_DIV as column Z

<regel><wenn wert="MisTop50RankingTop50CCP" /><dann wert="T50" /><dann wert="TUKB901" /><dann wert="PA_T50_TOP50" /><dann wert="TUKB203" /><dann wert="T_T50_TOP50" /><dann wert="STICHTAG" /><dann wert="CCP" /><dann wert=" " /><dann wert=" " /><dann wert="1" /><dann wert=" " /><dann wert=" " /><prio wert="50"/></regel>
<regel><wenn wert="MisScLeDivProfit" /><dann wert="SC" /><dann wert="TUKB901" /><dann wert="PA_SC_LE_DIV" /><dann wert="TUKB201" /><dann wert="T_SC_LE_DIV" /><dann wert="STICHTAG_MANDANT_LAUF" /><dann wert=" " /><dann wert=" " /><dann wert=" " /><dann wert="1" /><dann wert=" " /><dann wert=" " /><prio wert="50"/></regel>

Any idea would help a lot

Thank you

How do you know which `` is to go into column Y? Is it always the second `dann` element? Similarly, is column Z always the 3rd `dann` element? — MT0, Apr 12 '17 at 12:38

Wernfried Domscheit · Answer 1 · 2017-04-12T12:24:57.340

0

Why do you like to use Regular Expressions? You can do it also with XML functions. Assuming each line shall return one record you could use this one:

WITH x AS (
    SELECT XMLTYPE('<regel><wenn wert="MisTop50RankingTop50CCP" /><dann wert="T50" /><dann wert="TUKB901" /><dann wert="PA_T50_TOP50" /><dann wert="TUKB203" /><dann wert="T_T50_TOP50" /><dann wert="STICHTAG" /><dann wert="CCP" /><dann wert=" " /><dann wert=" " /><dann wert="1" /><dann wert=" " /><dann wert=" " /><prio wert="50"/></regel>') AS regel
FROM dual)
SELECT WENN AS X,
    DANN_1 AS Y,
    DANN_3 AS Z
FROM x
    NATURAL JOIN XMLTABLE('/regel' PASSING regel COLUMNS
    WENN VARCHAR2(100) PATH 'wenn/@wert',
    DANN_1 VARCHAR2(100) PATH 'dann[1]/@wert',
    DANN_3 VARCHAR2(100) PATH 'dann[3]/@wert'
); 


X                       Y   Z
MisTop50RankingTop50CCP T50 PA_T50_TOP50

In you case it may look as this:

SELECT WENN AS X,
    DANN_1 AS Y,
    DANN_3 AS Z
FROM YOUR_TABLE
    NATURAL JOIN XMLTABLE('/regel' PASSING XMLTYPE(RULE) COLUMNS
    WENN VARCHAR2(100) PATH 'wenn/@wert',
    DANN_1 VARCHAR2(100) PATH 'dann[1]/@wert',
    DANN_3 VARCHAR2(100) PATH 'dann[3]/@wert'
);

edited Apr 12 '17 at 12:24

answered Apr 12 '17 at 12:21

Wernfried Domscheit

54,457
9
76
110

But I would need to use REGEXP_SUBSTR because will be part of a bigger select and this is what a client wants – John Apr 12 '17 at 12:23
Are you sure you want to use `NATURAL JOIN` and not `CROSS JOIN` or `LEFT OUTER JOIN XMLTABLE(...) ON (1=1)`? – MT0 Apr 12 '17 at 12:33
@MT0, they are all working. I am used to use `NATURAL JOIN` when working with `XMLTABLE(...)` and nested tables `TABLE(...)` – Wernfried Domscheit Apr 12 '17 at 12:45
If the `YOUR_TABLE` table has a column `WENN` and the `XMLTABLE` generates a column `WENN` then `NATURAL JOIN` will try to join them both on that column (and probably fail). Using `CROSS JOIN` will bypass this. `CROSS JOIN` would, however, exclude rows where the initial XPATH finds no matches and the `LEFT OUTER JOIN` solution fixes this. – MT0 Apr 12 '17 at 12:48
Much appreciated all you ideas but can you please use REGEXP_SUBSTR and build it? Thank you – John Apr 12 '17 at 12:53
Right, I never faced such situation. `CROSS JOIN` will also fail unless you prefix the column with table alias. – Wernfried Domscheit Apr 12 '17 at 12:55

score 0 · Accepted Answer · edited May 23 '17 at 12:10

0

I would need to use REGEXP_SUBSTR because will be part of a bigger select and this is what a client wants

Really, do not use regular expressions to parse XML and use a proper XML parser.

If you are mandated to against all good sense:

SELECT REGEXP_SUBSTR( rule, '<wenn\s+wert\s*=\s*"(.*?)"\s*/>', 1, 1, NULL, 1 ) AS X,
       REGEXP_SUBSTR( rule, '<dann\s+wert\s*=\s*"(.*?)"\s*/>', 1, 2, NULL, 1 ) AS Y,
       REGEXP_SUBSTR( rule, '<dann\s+wert\s*=\s*"(.*?)"\s*/>', 1, 3, NULL, 1 ) AS Z
FROM   your_table;

edited May 23 '17 at 12:10

Community

1
1

answered Apr 12 '17 at 12:46

MT0

143,790
11
59
117

Thank you so much! It's working and saved my ass big time – John Apr 12 '17 at 14:02

Oracle SQL: Regular expression - retrive from a row specific characters

2 Answers2