January 12, 2021 at 5:07 pm
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
January 12, 2021 at 5:14 pm
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
January 12, 2021 at 7:14 pm
Thanks Thom, that looks much nicer and works well!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply