RegExp_Replace only numeric words in PLSQL

Question

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

Answer 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.