Hello All,
kindly, What's the best performance way to run a query so that spaces in the fields are ignored?
WITH ID_TABLE AS
(
SELECT ' 1' AS ID, 'AA' AS DESC1,'BB' AS DESC2 FROM DUAL
UNION ALL
SELECT '2' AS ID, 'CC' AS DESC1,'DD' AS DESC2 FROM DUAL
UNION ALL
SELECT ' 3' AS ID, 'FF' AS DESC1,'NN' AS DESC2 FROM DUAL
UNION ALL
SELECT '4' AS ID, 'MM' AS DESC1,'II' AS DESC2 FROM DUAL
UNION ALL
SELECT '5' AS ID, 'OO' AS DESC1,'PP' AS DESC2 FROM DUAL
),
INFO AS
(
SELECT ' 1' AS ID1,'2' AS ID2,' 3' AS ID3 , 'YYY' AS DESC_INFO FROM DUAL
UNION ALL
SELECT ' 4' AS ID1,'5' AS ID2,NULL AS ID3 , 'ZZZ' AS DESC_INFO FROM DUAL
)
SELECT
C.DESC1 || ' ' || C.DESC2 AS INFO1,
C1.DESC1 || ' ' || C1.DESC2 AS INFO2,
C2.DESC1 || ' ' || C2.DESC2 AS INFO3
FROM INFO I
LEFT JOIN ID_TABLE C ON (replace( C.ID,' ','') = replace (I.ID1,' ','') )
LEFT JOIN ID_TABLE C1 ON (replace( C1.ID,' ','')= replace (I.ID2,' ','') )
LEFT JOIN ID_TABLE C2 ON (replace( C2.ID,' ','')= replace (I.ID3,' ','') )
Output
INFO1 INFO2 INFO3
--------- ------------ ------------
AA BB CC DD FF NN
MM II OO PP
Thanks in advance
Oracle Database 11g