Home Forums SQL Server 7,2000 SQL Server Newbies Removing Carriage Returns in a Select Statement RE: Removing Carriage Returns in a Select Statement

  • October 12, 2007 at 5:23 pm

    #741753

    here's a find and replace for a TEXT field in SQL 2000;

    in this example, i'm replacing a relative link with a full link, so it's a good example:

    DECLARE @reviewid int, @ptr binary(16), @idx int

    SET @reviewid = 0

    SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body) - 1

    FROM reviews

    WHERE PATINDEX('%href="reviews%',review_body) > 0

    WHILE @reviewid > 0

    BEGIN

    SELECT @ptr = TEXTPTR(review_body)

    FROM reviews

    WHERE reviewid = @reviewid

    UPDATETEXT reviews.review_body @ptr @idx 13 'href="http://www.somewebsite.com/reviews'

    SET @reviewid = 0

    SELECT TOP 1 @reviewid = reviewid, @idx = PATINDEX('%href="reviews%',review_body)-1

    FROM reviews

    WHERE reviewid > @reviewid

    AND PATINDEX('%href="reviews%',review_body) > 0

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!