Post reply

REPLACE on text column

  • October 6, 2003 at 7:57 am

    #84460

    Hi there,

    kind of brain dead today.

    How can I replace all occurence of <BR> 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]

  • October 6, 2003 at 8:36 am

    #477195

    this may help,

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

    v

  • October 6, 2003 at 8:47 am

    #477196

    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....

  • October 6, 2003 at 8:57 am

    #477202

    WHILE @@ROWCOUNT > 0

    BEGIN

    UPDATE TheTable SET TextCol =

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

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

    WHERE TextCol LIKE '%<BR>%'

    END

    --Jonathan



    --Jonathan

  • October 6, 2003 at 9:00 am

    #477203

    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]

  • October 6, 2003 at 9:18 am

    #477208

    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 <BR>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



    --Jonathan

  • October 22, 2003 at 7:01 am

    #479087

    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

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

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