|
Home > Archive > MS SQL Server New Users > November 2005 > Remove Comma from a Text Field
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Remove Comma from a Text Field
|
|
|
| Can anyone tell me how to remove a comma ',' from a text field? I am coping
records to a csv and it is separating my text field into two columns when it
finally reaches the CSV. SQL Server 2000 SP4. Txs.
Select notes
from rn_appointments
results:
Col 1 Col2
How do you --do
returns:
Server: Msg 279, Level 16, State 2, Line 1
The text, ntext, and image data types are invalid in this subquery or
aggregate expression.
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I tried:
UPDATE rn_appointments
SET notes = REPLACE(notes, ',', '')
and got this error:
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.
I tried : SELECT MAX(col1)+','+MIN(co
l1)
FROM some_table ;
and got this error:
Ray
| |
| mark sullivan 2005-11-20, 3:23 am |
| Ray,
It was hard to figure out if you really need to remove the comma or
just have it correctly read by your CSV file. Many CSV applications get
around this problem by using enclosing quotes around fields that contain a
comma. If that is the case with your application try the following:
select '"' + notes + '"' as notes from rn_appointments.
If it is not clear I am wrapping the notes field in double quotes for
the CSV file and wrapping the double quotes with single quotes for the SQL
parser.
Hope this helps,
Mark
"Ray" <ray.smith@loislaw.com> wrote in message
news:4B583E2A-2F6D-47CE-95E0- 85481205437B@microso
ft.com...
> Can anyone tell me how to remove a comma ',' from a text field? I am
> coping
> records to a csv and it is separating my text field into two columns when
> it
> finally reaches the CSV. SQL Server 2000 SP4. Txs.
>
> Select notes
> from rn_appointments
> results:
> Col 1 Col2
> How do you --do
>
> returns:
> Server: Msg 279, Level 16, State 2, Line 1
> The text, ntext, and image data types are invalid in this subquery or
> aggregate expression.
> Server: Msg 403, Level 16, State 1, Line 1
> Invalid operator for data type. Operator equals add, type equals text.
>
> I tried:
> UPDATE rn_appointments
> SET notes = REPLACE(notes, ',', '')
>
> and got this error:
> Server: Msg 8116, Level 16, State 1, Line 1
> Argument data type text is invalid for argument 1 of replace function.
>
>
> I tried : SELECT MAX(col1)+','+MIN(co
l1)
> FROM some_table ;
> and got this error:
>
>
>
>
> Ray
|
|
|
|
|