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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy