February 20, 2003 at 9:55 am
I know you can use "replace" to remove carriage return or line feed from a varchar field but I am not sure how to do this for a text field. Any suggestions would be appreciated.
February 21, 2003 at 9:35 am
Here is one possible solution. I'm guessing that someone else might have a better way. This particular example removes all the carriage return and line feed from a text field, in multiple records. Let me know if this works for you:
create table x (page int, test_text text)
insert into x values (1,'line 1'+ char(13)+ char(10) + 'line 2' + char(13)+ char(10) + 'line 3')
insert into x values (2,'line 1'+ char(13)+ char(10) + 'line 2' + char(13)+ char(10) + 'line 3')
insert into x values (3,'line 1'+ char(13)+ char(10) + 'line 2')
insert into x values (4,'line 1'+ char(13)+ char(10) + 'line 2' + char(13)+ char(10) + 'line 3' + char(13)+ char(10) + 'line 4')
insert into x values (5,'line 1'+ char(13)+ char(10) + 'line 2' + char(13)+ char(10) + 'line 3')
insert into x values (6,'line 1'+ char(13)+ char(10) + 'line 2' + char(13)+ char(10) + 'line 3')
select * from x
declare @I int
declare @r int
declare @t int
declare @cmd nvarchar(1000)
DECLARE @ptrval binary(16)
set @r = 1
select @t=count(*) from x
set @i=0
while @r <= @t
begin
set @cmd = 'SELECT top ' + cast(@r as char) + ' @i=patindex(''%'' + char(13) + char(10) + ''%'',test_text)-1 FROM X'
exec sp_executesql @cmd,N'@i int out',@i out
while @i > 0
begin
set @cmd = 'select top ' + cast(@r as char) + ' @ptrval = TEXTPTR(test_text) from x ' + char(13) +
'updatetext x.test_text @ptrval ' + cast(@i as char) + ' 2 '''''
exec sp_executesql @cmd,N'@ptrval binary(16)',@ptrval = 1
set @cmd = 'SELECT top ' + cast(@r as char) + ' @i=patindex(''%'' + char(13) + char(10) + ''%'',test_text)-1 FROM X'
exec sp_executesql @cmd,N'@i int out',@i out
end
end
select * from x
drop table x
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 21, 2003 at 11:29 am
An easier way would be:
UPDATE Table
SET TextField = REPLACE(REPLACE(CONVERT(VARCHAR(8000), TextField), CHAR(13), ''), CHAR(10), '')
Not sure if you'd have to use Greg's code for fields where LEN(TextField) > 8000. Greg, any thoughts?
February 21, 2003 at 12:12 pm
It seems the best way would be to bring the data to the middle tier application, perform text manipulations there and put data back to SQL Server. While this is not a pure back end solution, the code would be simple, clean and fast. Wouldn't you agree? Of course, this is a good idea only if text is longer than 8000.
Michael
February 21, 2003 at 12:34 pm
Looks like you have a number of examples. If your text data is really less than 8000 then looks like jpipes suggested a simple approach. Now I guess I might ask why you are using text if the data is less than 8000 in length.
As far a moving it to another server that approach would work as well, but I suppose it also might have it's drawbacks associated with export/import issues.
Now thinking about the application tier, clearly some data validation routines to avoid getting CR/LF's would be a good approach and eliminate the need to manipulate your data.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply