SQL update to replace undetermined string value

  • Hello,

    I have a varchar column called "Message".  Sometimes, this column contains a bit of text that begins with "**" and ends with "**".  There can be text before and after, but what I'm looking to do is only remove the bits of text that follow the "**%**" pattern (which should only ever happen once within the column).

    I have a solution that is almost certainly very overcomplicated.  I'm hoping to find something much more elegant.

    CREATE TABLE
    #Messages
    (
    Message VARCHAR(80)
    )

    INSERT INTO #Messages (Message) VALUES ('Here is***Remove this*** my message')
    INSERT INTO #Messages (Message) VALUES ('How ya like ***Get rid of this***my message?')
    INSERT INTO #Messages (Message) VALUES ('***Eliminate***This is a message')
    INSERT INTO #Messages (Message) VALUES ('What follows***Purge this text*** is a message')
    INSERT INTO #Messages (Message) VALUES ('Yet another message***Should no longer exist*** ')

    SELECT
    Message,
    FirstAsterisks = CHARINDEX('***' , Message, 0),
    SecondAsterisks = (CHARINDEX('***' , Message,CHARINDEX('***' , Message, 0) + 3) + 2),
    MessageUpdate = REPLACE(Message, SUBSTRING(Message, CHARINDEX('***' , Message, 0), (CHARINDEX('***' , Message,CHARINDEX('***', Message, 0) + 3) + 3)-CHARINDEX('***' , Message, 0)),'')

    FROM #Messages

    DROP TABLE #Messages

     

  • Assuming that if there is a *** to start a section, there is always a corresponding *** to end it, then I would actually use a could of CHARINDEXes in the FROM (using APPLY and VALUES) and then STUFF to remove the part of the string:

    SELECT M.Message,
    STUFF(M.Message, CI1.I,CI2.I - CI1.I + 3,'')
    FROM #Messages M
    CROSS APPLY (VALUES(CHARINDEX('***',M.Message)))CI1(I)
    CROSS APPLY (VALUES(CHARINDEX('***',M.Message,CI1.I+1)))CI2(I);

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Thanks Thom, that looks much nicer and works well!

Viewing 3 posts - 1 through 3 (of 3 total)

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