• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

FinD and Replace T-SQL Script Needed

I need a SQL script to find all line breaks in a text field and replace them with the string "<br>".  Lets call the field [Users].[TextBlob].  This is for SQL server 2000.
0
creativelogic
Asked:
creativelogic
1 Solution
 
Patrick MatthewsCommented:
Hi creativelogic,

How are your line breaks defined?  Line feed, carriage return, or both?

Regards,

Patrick
0
 
creativelogicAuthor Commented:
lets say both
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi creativelogic,

Here is the query



-- create table Users ( i int, TextBlob text)
-- go
-- insert into Users select '1', 'Hello
-- world
-- this
-- is
-- SQL '

DECLARE @old varchar(20) ,
@new varchar(20)
SELECT @old = char(13),
@new = '<br>'

DECLARE @i int
DECLARE @txtPtr binary(16) ,
    @offset int ,
    @dellen int

SELECT @dellen = len(@old)
SELECT @txtPtr = 0

WHILE @txtPtr IS NOT NULL
BEGIN
    SELECT @txtPtr = null
    SELECT TOP 1 @i = i, @txtPtr = textptr(TextBlob)
    FROM Users
    WHERE TextBlob LIKE '%' + @old + '%'
   
    IF @txtPtr IS NOT NULL
    BEGIN
        SELECT @offset = 1
        WHILE @offset <> 0
        BEGIN
            SELECT @offset = patindex('%' + @old + '%', TextBlob)
            FROM Users
            WHERE i = @i
            --SELECT offset = @offset
           
            IF @offset > 0
            BEGIN
                SELECT @offset = @offset-1
                UPDATETEXT Users.TextBlob @txtPtr @offset @dellen @new
            END
        END -- end while
    END-- End if
END-- end while




SELECT * FROM Users


Aneesh R!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now