August 10, 2006 at 4:27 pm
Hi All
I have a table that contains "carriage returns" and "line feeds" in a column. I'd like to convert them into a pipe symbol.
Thanks for any and all help on this
William
August 10, 2006 at 4:41 pm
UPDATE table
SET column = REPLACE(REPLACE(column, CHAR(10), '|'), CHAR(13), '|')
The CHAR() function can find line feeds and carriage returns.
August 10, 2006 at 4:55 pm
Seems that should work, however returning this error when running this:
UPDATE bignotes
SET bignote = REPLACE(REPLACE(bignote, CHAR(10), '|'), CHAR(13), '')
Msg 8116, Level 16, State 1, Line 4
Argument data type text is invalid for argument 1 of replace function.
Thanks
William
August 10, 2006 at 5:06 pm
Database is not the place where files must be edited.
There are text editors for this.
_____________
Code for TallyGenerator
August 10, 2006 at 5:53 pm
The error message is saying that you are trying to edit a field of the 'text' data type. You will have to use the UPDATETEXT statement instead. Refer to BOL for more details.
August 15, 2006 at 11:53 am
Hi All:
Thanks for your help. This is what I came up with that sems to work well
William
drop table #temp
CREATE TABLE #temp (rowid int,textcol ntext)
INSERT INTO #temp values (1,'aaa bbb ccc ddd ccc')
INSERT INTO #temp values (2,'aaa bbb cc ddd eee')
INSERT INTO #temp values (3,'fff ggg ccc iii ccc')
select * from #temp
DECLARE @from nvarchar(100)
, @to nvarchar(100)
, @pos int
, @len int
, @rowid int
DECLARE @ptrval binary(16)
SET @from = 'ccc'
SET @to= 'hhh'
SET @len = LEN(@from)
SET @rowid = 0
SELECT @rowid = rowid
,@pos = CHARINDEX(@from, textcol) - 1
FROM #temp
WHERE CHARINDEX(@from, textcol) > 0
WHILE (@rowid > 0)
BEGIN
SELECT @ptrval = TEXTPTR(textcol)
FROM #temp
WHERE rowid = @rowid
UPDATETEXT #temp.textcol @ptrval @pos @len @to
SET @rowid = 0
SELECT @rowid = rowid
,@pos = CHARINDEX(@from, textcol) - 1
FROM #temp
WHERE CHARINDEX(@from, textcol) > 0
END
SELECT * FROM #temp
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply