Hi All,
I am TRYING to build an SQL that will convert a string passed as
HP|250 GB * 2 + 80 GB * 3 + 100 GB | SATA
to
HP | 250 GB | SATA
HP | 250 GB | SATA
HP | 80 GB | SATA
HP | 80 GB | SATA
HP | 80 GB | SATA
HP | 100 GB | SATA
My attempt so far is (which tells me to learn more about regexp)
WITH T AS
( SELECT q'[HP|250 GB * 2 + 80 GB * 3 + 100 GB | SATA]' str FROM DUAL
),
t2 AS
(SELECT trim(regexp_substr(str,'[^|]+',1,level)) val
FROM T
CONNECT BY level <= LENGTH (str)-LENGTH(REPLACE(str,'|'))+1
),t3 AS
(SELECT DISTINCT trim(regexp_substr(val,'[^+]+',1,level)) val
FROM t2 WHERE VAL LIKE '%*%' OR VAL LIKE '%+%'
CONNECT BY level <= LENGTH (val)-LENGTH(REPLACE(val,'+'))+1
),t4 as
(SELECT VAL,ROWNUM RN FROM T2 A1
WHERE VAL NOT LIKE '%*%' OR VAL NOT LIKE '%+%'),
t5 as
(SELECT A.VAL MK, T3.VAL CONFG, B.VAL TYP
FROM T3, (SELECT VAL FROM T4 WHERE RN = 1)A,(SELECT VAL FROM T4 WHERE RN = 2) B)
SELECT *
FROM T5;
And output I got so far is:
MK CONFG TYP
----------------------------------------- ----------------------------------------- -----------------------------------------
HP 80 GB * 3 SATA
HP 250 GB * 2 SATA
HP 100 GB SATA
Please suggest what more shall I do to get the desired output(An SQL)?
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Thanks for reading this post
*009*