June 5, 2003 at 10:49 am
I have a database table with a text field that contains carriage returns that I would like to replace with a comma and a space.
June 5, 2003 at 11:19 am
Use this to either update table in situ or create temp table from original and run query against temp table
declare @CT int
select @CT = count(*) from tablea where charindex(char(13),textcol) > 0
while (@ct > 0)
begin
update tablea set textcol=substring(textcol,1,charindex(char(13),textcol)-1)+', '+substring(textcol,charindex(char(13),textcol)+1,datalength(t)-charindex(char(13),textcol)-1)
from tablea where charindex(char(13),textcol) > 0
select @CT = count(*) from tablea where charindex(char(13),textcol) > 0
end
Thought of another possibility. If the maximum length of the text column is not greater than (8000-number of CR) then this would also work
select replace(convert(varchar,textcol),char(13),', ') from tablea
Edited by - davidburrows on 06/06/2003 02:16:02 AM
Far away is close at hand in the images of elsewhere.
Anon.
June 6, 2003 at 3:09 am
Hi DavidBurrows
just used your solution (although vice versa, replacing <br> with CHAR(13)+CHAR(10))
quote:
declare @CT intselect @CT = count(*) from tablea where charindex(char(13),textcol) > 0
while (@ct > 0)
begin
update tablea set textcol=substring(textcol,1,charindex(char(13),textcol)-1)+', '+substring(textcol,charindex(char(13),textcol)+1,datalength(t)-charindex(char(13),textcol)-1)
from tablea where charindex(char(13),textcol) > 0
select @CT = count(*) from tablea where charindex(char(13),textcol) > 0
end
just a small correction in the typo, I guess datalength(t) should be datalength(textcol)
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply