Search & Replace a string in Text/nText Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Search & Replace a string in Text/nText Columns

Author

Topic

danedickey
Starting Member

6 Posts

Posted - 2003-09-10 : 10:23:18

I have a table with a number of text columns. The text contains carriage return/line feed characters (char(13) + char(10)). I would like to go through my text columns and replace these with <BR> (HTML break tag). Has anyone had this same issue, and if so, any suggestions or ideas?

Thanks

Dane Dickey

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-09-10 : 11:02:58

What about this one ?


CREATE TABLE #temp (rowid int,textcol ntext) 
INSERT INTO  #temp values (1,''aaa bbb ccc ddd eee'') 
INSERT INTO  #temp values (2,''aaa bbb cc ddd eee'') 
INSERT INTO  #temp values (3,''fff ggg ccc iii jjj'') 
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 
DROP TABLE #temp

You have to modify this a little bit

Frank

danedickey
Starting Member

6 Posts

Posted - 2003-09-10 : 11:20:43

Thanks. I tried the code "as is" using your temp table, just to get a feel for it. When I run it in QA, it errors where you have the two single quotes:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'aaa'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'aaa'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'fff'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'ccc'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'hhh'.

So, a couple of questions:
1 - Why the 2 single quotes?
2 - Do I need to change a setting in QA, or just remove one of each pair of quotes?

Dane Dickey

danedickey
Starting Member

6 Posts

Posted - 2003-09-10 : 11:27:12

Removing one of each pair seemed to do the trick on your example. Works good with the temp table. I modified the code for my table, and of course, got errors. Here is my modified code:

-- Here is the table
-- CREATE TABLE Dane_Text_FindReplace (Dane_ID int, Dane_Text text)

DECLARE @from nvarchar(100)
, @to nvarchar(100)
, @pos int
, @len int
, @rowid int
DECLARE @ptrval binary(16)
SET @from = char(13) + char(10)
SET @to= '<br>'
SET @len = LEN(@from)
SET @rowid = 0
SELECT @rowid = Dane_ID
,@pos = CHARINDEX(@from, Dane_Text) - 1
FROM Dane_Text_FindReplace
WHERE CHARINDEX(@from, Dane_Text) > 0

WHILE (@rowid > 0)
BEGIN
SELECT @ptrval = TEXTPTR(Dane_Text)
FROM Dane_Text_FindReplace
WHERE Dane_ID = @rowid

UPDATETEXT Dane_Text_FindReplace.Dane_Text @ptrval @pos @len @to
SET @rowid = 0
SELECT @rowid = Dane_ID
,@pos = CHARINDEX(@from, Dane_Text) - 1
FROM Dane_Text_FindReplace
WHERE CHARINDEX(@from, Dane_Text) > 0
END

SELECT * FROM Dane_Text_FindReplace

And Here are the errors I get (one for each of the 4 rows of data):

Server: Msg 257, Level 16, State 3, Line 16
Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 16
Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 30
Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 30
Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query.

Thanks - we're definitely close!

Dane Dickey

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-09-11 : 02:13:52

err, yes typos on my side...
Copy and paste is sometimes anything but easy

Changing
CREATE TABLE Dane_Text_FindReplace (Dane_ID int, Dane_Text text)
to
CREATE TABLE Dane_Text_FindReplace (Dane_ID int, Dane_Text ntext)

lets QA complete the query

Frank

danedickey
Starting Member

6 Posts

Posted - 2003-09-26 : 14:53:38

Excellent! Thanks Frank!

Dane Dickey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources