Hi I want to replace following examples with the following:

• (LTRIM) Replace any space at the beginning before first Alphanumerical character
• (RTRIM) Replace any space at the end after last Alphanumerical character
• Replace any non-alpha numerical with space
• Replace any empty (not null) with word "UNKNOWN"
• In case of only space replace all spaces with word "UNKNOWN"

'    abc  ' -> 'abc'
'abc      ' -> 'abc'
'  abc    ' -> 'abc'
'!ab c  ? ' -> 'ab c'
' a b c   ' -> 'a b c' 
'a!b?c  $ ' -> 'a b c'
'         ' -> 'UNKNOWN'
''          -> 'UNKNOWN'
null        -> null 

These are the queries I'm working on but I'm not getting anywhere:

select 
'-|&@/,.‘“<>():;' as default1,
translate(SUBSTR(MAX ('-|&@/,.‘“<>():;'),1,70), '-|&@/,.‘“<>():;', '               ') as formatted1
from dual;


select trim(regexp_replace(regexp_replace(' a b cdefgh ' , '[[:space:]]*',''), '(.)', '\1 UNK' )) as formatted from dual;

SELECT LTRIM(RTRIM('    NEXT LEVEL EMPLOYEE    ')) from dual;

CodePudding user response：

Replace any empty (not null) with word "UNKNOWN"

This is impossible as, in Oracle, there are no empty strings as an empty string is stored as NULL.

Apart from that impossibility, you can use:

SELECT value,
       CASE
       WHEN value IS NULL
       THEN NULL
       ELSE COALESCE(
              TRIM(
                BOTH ' ' FROM
                REGEXP_REPLACE(
                  value,
                  '[^A-Za-z0-9]',
                  ' '
                )
              ),
              'UNKNOWN'
            )
       END AS updated_value
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value) AS
  SELECT '    abc  ' FROM DUAL UNION ALL
  SELECT 'abc      ' FROM DUAL UNION ALL
  SELECT '  abc    ' FROM DUAL UNION ALL
  SELECT '!ab c  ? ' FROM DUAL UNION ALL
  SELECT ' a b c   ' FROM DUAL UNION ALL
  SELECT 'a!b?c  $ ' FROM DUAL UNION ALL
  SELECT '         ' FROM DUAL UNION ALL
  SELECT '' FROM DUAL UNION ALL
  SELECT null FROM DUAL;

Outputs:

fiddle