REPLACE on text column

Question

Post reply

REPLACE on text column

Frank Kalis

SSC Guru

Points: 111183
More actions
October 6, 2003 at 7:57 am

#84460

Hi there,
kind of brain dead today.
How can I replace all occurence of with vbCrLf within a text column???
Frank
http://www.insidesql.de
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vijju2k SSCommitted Points: 1531 More actions · Answer 1

this may help,

http://www.sqlmag.com/Forums/messageview.cfm?catid=8&threadid=8135

v

winash SSChampion Points: 11446 More actions · Answer 2

This might work....

Declare @TextPtr varbinary(16)

Declare @StartPosition Int

SET @StartPosition = 1

SELECT @TextPtr = TEXTPTR(TextColumnName) FROM TableName WHERE <WHERE CLAUSE>

WHILE @StartPosition > 0

BEGIN

Select @StartPosition = Charindex(Char(13),TextColumnName)-1 -- same for Char(10)???

FROM TableName WHERE <WHERE CLAUSE>

IF @StartPosition > 0

UPDATETEXT TableName.TextColumnName @TextPtr @StartPosition 1 'VBCRLF'

END

HTH....

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

WHILE @@ROWCOUNT > 0

BEGIN

UPDATE TheTable SET TextCol =

SUBSTRING(TextCol,1,PATINDEX('% %',TextCol) - 1) +'vbCrLf' +

SUBSTRING(TextCol,PATINDEX('% %',TextCol)+4, DATALENGTH(TextCol) - PATINDEX('% %',TextCol))

WHERE TextCol LIKE '% %'

END

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Thanks for the answers so far.

vbCrLf is the VB constant for carriage return plus a line feed, so I guess I have to play with CHR(10)+CHR(13), right?

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

quote:
Thanks for the answers so far.
vbCrLf is the VB constant for carriage return plus a line feed, so I guess I have to play with CHR(10)+CHR(13), right?
Frank
http://www.insidesql.de

Ja. BTW, my solution only works if the length of the text before and after the s is less than 8000, as that's the data type that SUBSTRING() returns. Otherwise, you'd need to use a cursor with UPDATETEXT().

--Jonathan

Bent SSC Enthusiast Points: 116 More actions · Answer 6

quote:
Thanks for the answers so far.
vbCrLf is the VB constant for carriage return plus a line feed, so I guess I have to play with CHR(10)+CHR(13), right?
Frank
http://www.insidesql.de

Almost right - better use CHR(13) + CHR(10) (inverse order) for DOS compatibility. (CHR(13) is carriage return, and CHR(10) is line feed)

Saludos