SQL update to replace undetermined string value

• tarr94

  SSCommitted

  Points: 1684

  More actions

  January 12, 2021 at 5:07 pm

  #3831558

  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

   

• Thom A

  SSC Guru

  Points: 99137

  More actions

  January 12, 2021 at 5:14 pm

  #3831560

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

• tarr94

  SSCommitted

  Points: 1684

  More actions

  January 12, 2021 at 7:14 pm

  #3831602

  Thanks Thom, that looks much nicer and works well!