Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JasonD
Starting Member
6 Posts |
 Posted - 2005-11-14 : 11:01:19
|
| I have a field used to hold a persons full name. I am creating an extract from the database using SQL which will write to a csv file for use with excel (we have to use commas to separate as part of the given requirments).Because the commas are causing problems is there a way in SQL to automatically remove these from this column?Thanks. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-14 : 11:07:28
|
| Use the REPLACE function. Check SQL Server Books Onlue for more info on this function:replace(MyColumn,',','')CODO ERGO SUM |
 |
|
|
JasonD
Starting Member
6 Posts |
Posted - 2005-11-14 : 11:44:09
|
| I looked at some examples on the web but not realt hot on the SQL.I tried the following but get an error:, SELECT REPLACE(C.NAME,',','') AS EXTRACTNAME,Server: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'SELECT'. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-14 : 12:40:28
|
| You should be able to leave the commas in the name, so long as you specify a string delimeter (usually " ") in your export format.But I don't recommend storing full names as a single string in the first place, so you should consider parsing it out in your database. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-14 : 13:24:25
|
| , REPLACE(C.NAME,',','') AS EXTRACTNAME,or , '"' + C.NAME + '"' AS EXTRACTNAME,would be quote delimitted so sort of correct but don't think excel can deal with it.You could tab delimit - that's often better for the import.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Extracting commas from a field