MS SQL Server New Users - Help Please!! Job depends on it!!

Author

Help Please!! Job depends on it!!

Ray

2005-11-17, 1:23 pm

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. It is also wrapping some
rows to 2 or 3 lines.

My query I am using is:
Select notes
from rn_appointments
results:

1 Row should Read: How do you, do
It is coming out as:
Col 1 Row 1 Col2 Row 2
How do you do

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 checked BOL and couldn't understand what to use.

Thanks.

--
Ray

Adam Machanic

2005-11-17, 8:24 pm

http://www.sqlteam.com/item.asp?ItemID=15528

--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

"Ray" <ray.smith@loislaw.com> wrote in message
news:01C62D3F-2F60-4A39-BE4F- F6C14711D72A@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. It is also wrapping some
> rows to 2 or 3 lines.
>
> My query I am using is:
> Select notes
> from rn_appointments
> results:
>
> 1 Row should Read: How do you, do
> It is coming out as:
> Col 1 Row 1 Col2 Row 2
> How do you do
>
> 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 checked BOL and couldn't understand what to use.
>
> Thanks.
>
> --
> Ray

Ray

2005-11-18, 11:24 am

Thanks Adam.

That helped get rid of the comma ',' and now I am working on getting rid of
crlf, data exceeding the length of the field, etc.

I found that you can copy the table to access, save the file to a txt,
import the file back into access and it lets you clearn all the bad stuff at
once. Then you can import the table back to sql all cleaned up.

What is your opinion on doing this? I know it is now standard SQL.
--
Ray

"Adam Machanic" wrote:

> http://www.sqlteam.com/item.asp?ItemID=15528
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
> "Ray" <ray.smith@loislaw.com> wrote in message
> news:01C62D3F-2F60-4A39-BE4F- F6C14711D72A@microso
ft.com...
>
>
>

Sponsored Links