Oracle regex for remove single letters-CodePudding

I need to remove spaces with single letters. Example :- select 'A D N WARUNA' from dual; output maybe 'ADN WARUNA' select 'W M P NUWAN Bandara' from dual; output maybe 'WMP NUWAN Bandara'

CodePudding user response：

Here's one option; read comments within code

SQL> WITH
  2     test (col)
  3     AS
  4     -- sample data
  5        (SELECT 'A D N WARUNA' FROM DUAL
  6         UNION ALL
  7         SELECT 'W M P NUWAN Bandara' FROM DUAL),

  8     temp
  9     AS
 10     -- split COL to rows (delimiter is a space character; add one if length of
 11     -- the substring is larger than 1
 12        (SELECT col,
 13                COLUMN_VALUE CV,
 14                   CASE
 15                      WHEN LENGTH (REGEXP_SUBSTR (col,
 16                                                  '[^ ] ',
 17                                                  1,
 18                                                  COLUMN_VALUE)) > 1
 19                      THEN
 20                         ' '
 21                   END
 22                || REGEXP_SUBSTR (col,
 23                                  '[^ ] ',
 24                                  1,
 25                                  COLUMN_VALUE) let
 26           FROM test
 27                CROSS JOIN
 28                TABLE (
 29                   CAST (
 30                      MULTISET (
 31                             SELECT LEVEL
 32                               FROM DUAL
 33                         CONNECT BY LEVEL <= REGEXP_COUNT (col, ' ')   1)
 34                         AS SYS.odcinumberlist)))

 35  -- finally, aggregate the result with NO DELIMITER for LISTAGG - space is already
 36  -- added where necessary in previous CTE
 37    SELECT col, LISTAGG (let, NULL) WITHIN GROUP (ORDER BY CV) result
 38      FROM temp
 39  GROUP BY col;

COL                 RESULT
------------------- ------------------------------
A D N WARUNA        ADN WARUNA
W M P NUWAN Bandara WMP NUWAN Bandara

SQL>

CodePudding user response：

You do not need (slow) regular expressions and can use simple string functions:

WITH bounds (rid, value, spos, epos, nepos) AS (
  SELECT ROWID,
         value,
         1,
         INSTR(value, ' ', 1, 1),
         INSTR(value, ' ', 1, 2)
  FROM   table_name
UNION ALL
  SELECT rid,
         value,
         epos   1,
         nepos,
         INSTR(value, ' ', nepos   1)
  FROM   bounds
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY value SET order_rn
SELECT LISTAGG(
         CASE
         WHEN epos = 0
         THEN SUBSTR(value, spos)
         WHEN nepos > spos   3
         OR   nepos = 0 AND LENGTH(value) > spos   3
         THEN SUBSTR(value, spos, epos - spos   1)
         ELSE SUBSTR(value, spos, epos - spos)
         END
       ) WITHIN GROUP (ORDER BY ROWNUM) AS value
FROM   bounds
GROUP BY rid;

Which, for the sample data:

CREATE TABLE table_name (value) AS 
SELECT 'A DN WARUNA' FROM DUAL UNION ALL
SELECT 'A D NWARUNA' FROM DUAL UNION ALL
SELECT 'A D N WARUNA' FROM DUAL UNION ALL
SELECT 'A D N W ARUNA' FROM DUAL;

Outputs:

VALUE

A DN WARUNA

AD NWARUNA

ADN WARUNA

ADNW ARUNA

VALUE
A DN WARUNA
AD NWARUNA
ADN WARUNA
ADNW ARUNA

db<>fiddle here