How do I replace a value in a string?

[sbroomfield]

Hi,

I have some SQL that I run to create output to a csv file.
This is mailed out to my users and when they open the file it opens in Excel.
This is all very good, but two of the columns contain strings that may include commas at any point in the string. This obviously buggers up the output!
I have looked at the possibility of amending those fields on the database but there are 3000 instances of these strings containing commas so I had better think about manipulating the data once I have retrieved it from the database.
Is there a way that I can specify that if there is a comma anywhere in the string, replace it with a space?

Hope someone can help,

Thanks,

Steve

 

[grega]

Which DB? What platform are you running the DB on (Win/Unix/)? Depending on the answers to these questions, there are various ways ...

Greg.

 

[sbroomfield]

Greg,

Running Sybase on UNIX.



Thanks,

Steve

 

[olded]

Steve:

It's probably not an ansi standard, but most databases (and I think sybase has it), support a replace function:

Select Replace(FileName,'.txt','ECR.txt')
From table

For more info, see thread220-184306 or thread220-267500.

Regards,


Ed

 

[sbroomfield]

ED,

I've tried your methods:

replace(Old_INST,","," &quot,

and:
replace(Old_INST,char(2),",&quot,

and I get Incorrect syntax near the keyword 'replace'.

??

 

[ThomVF]

When I create CSV files,I usually concatenate quotes around strings that could have commas. When you import the CSV to Excel,it uses the quotes as a delimter of the string.

string1,string2,"string 3, potentially having commas",string4

Tom =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[angiole]

If you have the capability to specifiy your own delimiter, you may try the tilde (~) since it's unlikely(?) to be used.

The import to Excel would require the tilde to be specified as a delimiter, but does not mess up the import.

AA 8~) AA 8~)