0

I need help to replace the numeric words with null.

Example:

  1. Dustun 0989 LLC
  2. Dustun_0989 LLC
  3. 457 Dustun LLC
  4. 457_Dustun LLC
  5. 334 Dunlop 987

Output:

  1. Dustun LLC
  2. Dustun_0989 LLC
  3. Dustun LLC
  4. 457_Dustun LLC
  5. Dunlop
brenners1302
  • 1,440
  • 10
  • 18
meet
  • 5
  • 1
  • 3
  • no .. the below answer provided by you is good enough and it works like a charm...many thanks. – meet Oct 26 '15 at 07:51

3 Answers3

1

You could get it done with regular expressions. For example, something like this:

WITH the_table AS (SELECT 'Dustun 0989 LLC' field FROM dual
                   UNION
                   SELECT 'Dustun_0989 LLC' field FROM dual
                   UNION 
                   SELECT '457 Dustun LLC' field FROM dual
                   UNION
                   SELECT '457_Dustun LLC' field FROM dual
                   UNION
                   SELECT 'Dunlop 987' field FROM dual
                   UNION
                   SELECT '222 333 ADIS GROUP 422 123' field FROM dual)                   
SELECT field, TRIM(REGEXP_REPLACE(field,'((^|\s|\W)(\d|\s)+($|\s|\W))',' '))
FROM the_table

Note that (^|\s|\W) and ($|\s|\W) are Oracle regexp equivalent to \b, as explained in Oracle REGEXP_LIKE and word boundaries

Where:

  • (^|\s|\W) is either the beginning of line, a blank space or a non-word character.
  • (\s|\d)+ is a combination of one or more digits and spaces.
  • ($|\s|\W) is either the end of line, a blank space or a non-word character.
Community
  • 1
  • 1
pablomatico
  • 2,222
  • 20
  • 25
0

No need for PL/SQL here, a simple SQL statement will do:

regexp_replace(the_column, '(\s[0-9]+\s)|(^[0-9]+\s)|(\s[0-9]+$)', ' ')

This does the replaces any number of digits between two whitespaces or digits at the start of the value followed by a whitespace or a whitespace followed by digits at the end of the input value.

The following: 

with sample_data (the_column) as 
(
    select 'Dustun 0989 LLC' from dual union all
    select 'Dustun_0989 LLC' from dual union all
    select '457 Dustun LLC' from dual union all
    select '457_Dustun LLC' from dual union all
    select '334 Dunlop 987' from dual
)
select regexp_replace(the_column, '(\s[0-9]+\s)|(^[0-9]+\s)|(\s[0-9]+$)', ' ') as new_value
from sample_data

will output: 

NEW_VALUE      
---------------
Dustun LLC     
Dustun_0989 LLC
 Dustun LLC    
457_Dustun LLC 
 Dunlop

To get rid of the leading (or trailing) spaces, use the trim function: trim(regexp_replace(...))

  • Can you modify it so that it should replace the numeric value for more that one consecutive occurance. – meet Oct 26 '15 at 14:20
  • 222 333 ADIS GROUP 422 123 is getting converted to 333 ADIS GROUP 123 – meet Oct 26 '15 at 14:20
0

This will work too

select regexp_replace(text,'[0-9]') from dual
Pang
  • 9,564
  • 146
  • 81
  • 122
Prabhat Sharma
  • 148
  • 1
  • 7
  • No, it will not work, because it will change `Dustun_0989 LLC` to `Dustun_ LLC` which is not what meet wants –  Oct 26 '15 at 08:22