stored procedure find and replace text in column

Posted on 2010-01-05
Last Modified: 2012-05-08
I'm guessing that it is possible to do this but I do not know how.  What I want to do is scan a text column, find a certain word in the text (not case sensitive if possible) and then insert text in a different column in the same table.  So, If column A contains the word "test" insert the word "boobs" in column B.  If you know how to do this please let me know!
Question by:schwientekd
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Hello schwientekd,

     update tablename
     set b = 'boobs'
     where  charindex('test', a)  >0


    LVL 5

    Expert Comment

    You mention that it is a text column. It the column is truely a "text" datatype, the charindex() function is not allowed and you should change it to varchar(max) first.
    LVL 6

    Expert Comment

    you can use this:

    UPDATE YourTable
           SET ColumnB = CASE WHEN (ColumnA like '%YourWordToFind%') THEN 'test' END
    LVL 6

    Accepted Solution

    UPDATE YourTable
           SET ColumnB = 'test'
    WHERE ColumnA like '%YourWordToFind%'


    Author Closing Comment

    This was exactly what I was looking for.  Easy to follow and worked perfectly!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now