CODEWITHSUNDEEP
sqloracle-database
user2786962
user2786962

Reputation: 469

Need to replace alpha numeric characters by space and create new column

I need to convert the following

Column 1          Column 2
ABC, Company   ABC Company
TA. Comp       TA Comp

How can I get Column2 in sql where I am removing all ',' '.' to space.

Upvotes: 0

Views: 54

Answers (3)

Slkrasnodar
Slkrasnodar

Reputation: 824

with t (val) as 
(
  select 'ABC,. Cmpany' from dual union all 
  select 'A, VC' from dual union all
  select 'A,, BC...com' from dual
)
select 
  val, 
  replace(replace(val, ',', ''), '.', '') x , -- one way
  regexp_replace(val, '[,.]', '') y           -- another way
from t
;

VAL             X          Y        
--------------- ---------- ----------
ABC,. Cmpany    ABC Cmpany ABC Cmpany
A, VC           A VC       A VC      
A,, BC...com    A BCcom    A BCcom

Upvotes: 0

tbone
tbone

Reputation: 15493

How about:

with testdata as (
  select 'ABC, Company Inc.' as col1 from dual
  union all
  select 'TA. Comp' as col1 from dual
)
select trim(regexp_replace(regexp_replace(col1, '[[:punct:]]',' '), ' {2,}', ' ')) as col2
from testdata;

Output:

ABC Company Inc
TA Comp

Assuming punctuation is what you're trying to blank out.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

You can try to use:

SELECT REGEXP_REPLACE(column1, '[^a-zA-Z0-9 ]+', '')
FROM DUAL

Upvotes: 0

Related Questions