Remove Carriage Return from Text Field 1

[bmacbmac]

Hi. I am trying to get rid of all carriage returns in a text field. I have tried the following query to replace a carriage return with ''

update mytable 
set comments =  REPLACE(SUBSTRING(comments, 1,  DATALENGTH(comments)), CHAR(13), '')
where charindex(char(13), Comments) > 0

I am DTS Exporting the data to a text file and the carriage return is still starting a new row. Can you help with my query or have a better idea in mind?

Thanks!

Brian

 

[RiverGuy]

Do you literally mean a Text datatype or a char/nchar/varchar/nvarchar data type? I assume the latter.

I would also suggest modifying the select query for the export in case you need the retain the original data in the table.

Something like:
SELECT REPLACE(comments, CHAR(13), '')

should be all you need.

 

[bborissov]

You should check for CHAR(10) also.

update mytable
       set comments =  REPLACE(REPLACE(comments, CHAR(13), '')
                               , CHAR(10), '')
where (charindex(char(13), Comments)+
      charindex(char(10), Comments)) > 0

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bmacbmac]

Thanks RiverGuy - Yes literally it's a Text datatype (I wish it wasn't). The select statement you gave me gives an error:

Argument data type text is invalid for argument 1 of replace function.

Bborissov - I'm also getting the same error on your update script.

Is there something I can add for a Text datatype?

 

[bborissov]

no, that will work only for char fields.
What version of SQL Server you use?
If you use SQL Server 2005 (or 2008) you could cast it to varchar(max).
If you use SQL Server 2000 you could cast it to varchar(8000).
Just hope your field didn't have more than 8000 bites in it.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RiverGuy]

Yes, definitely cast in your export query if you can. Otherwise, you can look at UPDATETEXT/WRITETEXT for a TEXT column.

 

[bmacbmac]

Thanks Bborissov - I'm using 2000. Your query actually gave me an idea. I also replaced the CHAR(10)'s with '' and it worked. Must have been a line feed.

Thanks as always.