Removing comma in a string

Database
uco
N/A

Removing comma in a string

Hi 

Can anyone let me know how I can achieve this

I have a column Bus_name varchar(55) in which some names have commas in them , they need to be removed while doing a select on the column

example

Bus_name

Shapiro,company,inc

Lincoln,Abraham,inc

Kegs Bar and grill,inc

output needed

Bus_name

Shapiro company inc

Lincoln Abraham inc

Kegs Bar and grill inc

Appreciate your help

7 REPLIES

Re: Removing comma in a string

Did you try with oreplace function?

oreplace(column_name, 'string_to_replace','new_string')

say: oreplace (col1,',','')

Cheers,

Raja

uco
N/A

Re: Removing comma in a string

Yes , It  works good when I select only one column , it did not work when I select other columns along with bus_name

Re: Removing comma in a string

You have not explained with other columns. Let me assume that you can try thus: select oreplace (col1,',','')||oreplace (col2,',','')||oreplace (col3,',','') from table1.

For space use ||' '|| say like select oreplace (col1,',','')||' '||.....

If you want to append with text then select  'collect stats on '

Cheers,

Raja

uco
N/A

Re: Removing comma in a string

I am having 

select cola , col b, orreplace(bus_name, ',' ,' ') col c,

 from table1

It is throwing error 

A column or character expression size is larger than max size 

Re: Removing comma in a string

Hi,

CAST the replaced column to smaller size,it will work for you

Re: Removing comma in a string

Check your syntax properly. I can see a comma after col c and also why (col space c, col c).

uco
N/A

Re: Removing comma in a string

orreplace with CAST worked,  thank you everyone