All Forums Database
uco 15 posts Joined 12/13
02 Jan 2014
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

Raja_KT 1246 posts Joined 07/09
02 Jan 2014

Did you try with oreplace function?

 

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

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

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

uco 15 posts Joined 12/13
02 Jan 2014

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

Raja_KT 1246 posts Joined 07/09
02 Jan 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

uco 15 posts Joined 12/13
02 Jan 2014

 
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 

khatriprad 12 posts Joined 12/10
02 Jan 2014

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

Pradeep

Raja_KT 1246 posts Joined 07/09
02 Jan 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

uco 15 posts Joined 12/13
03 Jan 2014

orreplace with CAST worked,  thank you everyone

You must sign in to leave a comment.