Karthik Karthik - 6 months ago 14
SQL Question

Oracle Replace String

I have data in a column like below.

ColumnA

APX-0150004-225 02A,APX-0150004-234 02A

APX-0170009-001 04C,APX-0170021-002 04C,APX-0170021-004 02R

APX-0170012-001 04C,APX-0170012-002 02R,APX-0170021-003 04C,APX-0170021-005 01R


And I want to replace every three characters before comma with null and output should be like below

ColumnA

APX-0150004-225 ,APX-0150004-234

APX-0170009-001 ,APX-0170021-002 ,APX-0170021-004

APX-0170012-001 ,APX-0170012-002 ,APX-0170021-003 ,APX-0170021-005

MT0 MT0
Answer

Oracle Setup:

CREATE TABLE table_name ( ColumnA ) AS
SELECT 'APX-0150004-225 02A,APX-0150004-234 02A' FROM DUAL UNION ALL
SELECT 'APX-0170009-001 04C,APX-0170021-002 04C,APX-0170021-004 02R' FROM DUAL UNION ALL
SELECT 'APX-0170012-001 04C,APX-0170012-002 02R,APX-0170021-003 04C,APX-0170021-005 01R' FROM DUAL;

Query:

SELECT REGEXP_REPLACE( ColumnA, '[^,]{3}(,|$)', '\1' ) AS ColumnA
FROM   table_name

Output:

COLUMNA
------------------------------------------------------------------
APX-0150004-225 ,APX-0150004-234
APX-0170009-001 ,APX-0170021-002 ,APX-0170021-004
APX-0170012-001 ,APX-0170012-002 ,APX-0170021-003 ,APX-0170021-005