CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-replace
svs
svs

Reputation: 11

How to remove spaces and special characters between numbers using Oracle regexp_replace

I need to remove all spaces and special characters between the numbers in the text field in Oracle and Netezza.

Input:
[Any text 00 00 111 1   2222 ,?/!@#$ 33333 any text 123,. 45]. 

Output:
[Any text 0000111222233333 any text 123.45]

Thanks!

Upvotes: 0

Views: 2407

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use

SELECT REGEXP_REPLACE(col,'[^0-9]') AS new_col
  FROM tab

or [:digit:] posix such as

SELECT REGEXP_REPLACE(col,'[^[:digit:]]') AS new_col
  FROM tab

in order to remove all non-digit characters including whitespaces.

Update : Depending your added request

SELECT ID,
       col,       
       CASE
         WHEN REGEXP_INSTR(col, '[[:digit:]]') != 1 THEN
          REPLACE(REGEXP_SUBSTR(col, '[^[:digit:][:punct:]]+'), ' ')
       END || 
       REGEXP_REPLACE(col, '[^0-9]*([0-9]+|$)', '\1') ||
       REGEXP_SUBSTR(REGEXP_REPLACE(col, '[[:punct:] ]'), '[^[:digit:]]+$') AS new_col
  FROM tab

would remove any non-digit character starting from the first digit upto the last one, and leaves the external parts unchanged.

Demo

Upvotes: 2

Related Questions