I need help to replace the numeric words with null.
Example:
- Dustun 0989 LLC
- Dustun_0989 LLC
- 457 Dustun LLC
- 457_Dustun LLC
- 334 Dunlop 987
Output:
- Dustun LLC
- Dustun_0989 LLC
- Dustun LLC
- 457_Dustun LLC
- Dunlop
-
no .. the below answer provided by you is good enough and it works like a charm...many thanks.meet– meet2015-10-26 07:51:50 +00:00Commented Oct 26, 2015 at 7:51
Add a comment
|
3 Answers
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.
2 Comments
meet
222 333 ADIS GROUP 422 123 is getting converted to 333 ADIS GROUP 123, ideally it should be ADIS GROUP
pablomatico
@meet I just edited my answer to include this case, hope it helps
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(...))
This will work too
select regexp_replace(text,'[0-9]') from dual
