Replace and remove text from string in sql server

Question

I have string like the following, it is sample

<b>22/10 - 10:47 - wes:</b> ###TaskDefId=46###N Enq - Send Quote<b><br/>29/10 - 15:55 - grn: Some Text ###TaskDefId=31### ABC - DEFG HIJ<b><br/>22/10 - 12:29 - white: ....

Please see below this is the complete string, the original looks like above, I have removed b and br tags to make it more clearer. At the end of each line I have write down that what I want to remove and what I want to replace.

22/10 - 10:47 - wes: ###TaskDefId=46###N Enq - Send Quote               '###TaskDefId=46###N Enq - Send Quote' This should be removed completely 
29/10 - 15:55 - grn: Some Text ###TaskDefId=31### ABC - DEFG HIJ            '###TaskDefId=31###' This should be replaced with 'STAGE CHNAGED:'
22/10 - 12:29 - white: Eamiled ###TaskDefId=31### Qtd - Email and Email     '###TaskDefId=31###Qtd - Email and Email' This should be removed completely because last stage was exactly same
29/10 - 16:26 - wesl: Spoke ###TOaskDefId=48### CTD - DDDDDD            '###TOaskDefId=48###' This should be replaced with 'STAGE CHANGED:' becaue last stage was different
09/12 - 13:12 - wesl: Spoke ###TaskDefId=31### CTD - DDDDDD         '###TaskDefId=31###' This should be replaced with 'STAGE CHANGED:' becaue last stage was different
15/11 - 18:41 - white: Some xxxx ###TaskDefId=31### DDD AND - CDD TDED      '###TaskDefId=31###' This should be removed.
09/12 - 13:12 - wesl: Balanced ###TaskDefId=23### SDTED DERDDW          '###TaskDefId=23###' This should be replaced with 'STAGE CHANGED:'

If question looks messy please copy and paste in notepad and I hope the question will make sense. Let me again tell you that this is one string not different rows.

EDIT:

This string is in one column of table and I need to update the same column after these changes.
As I have explained in my question that I don't just want to replace and remove the pattern but replace the ###TaskDefId=seomnumber### with 'STAGE CHANGED:' if last occurrence of the pattern and the current one is exactly same and removed if last occurrence of the pattern and the current one is different.

Thanks

Can you show us what your table structure looks like?? Most importantly: what datatype does this column have that contains this data?? — marc_s
– marc_s, Commented Dec 15, 2010 at 14:05
Thanks @marc_s. DataType is VARCHAR(MAX) and I have this string in one column named NotesHistory and I have to updated same column after these changes — Kashif
– Kashif, Commented Dec 15, 2010 at 14:24
This seems more fit at an application level (Especially with circumstances like only perform the replace is the previous one is the same). Find a language you're comfortable with and can connect to the database with, extract the data, perform your cleanup, then continue on. If there are a lot of rows, it may be wise to add a "cleaned_up" column so you can batch it off. — Brad Christie
– Brad Christie, Commented Dec 15, 2010 at 14:38
@Brad Christie, thanks for the suggestion and it looks like that this is the solution of this, because no sql guru has come with solution yet. — Kashif
– Kashif, Commented Dec 15, 2010 at 14:43
@Muhammad: It's not about being a guru--SQL is designed for storage and retrieval, not manipulation. There are "convenient" string functions integrated with SQL, but nothing heavy enough to do this kind of interrogation on the data stored within. — Brad Christie
– Brad Christie, Commented Dec 15, 2010 at 14:47

pavanred · Accepted Answer · 2010-12-15 14:05:27Z

1

You can use -

SELECT REPLACE ( string_expression , string_pattern , string_replacement )
GO

But in your string I see many patterns that you want to replace. I would suggest you can use something like -

Declare @String_Original nvarchar(max)
DECLARE @Replacement1 nvarchar(50)
DECLARE @Pattern1 nvarchar(50)

@String_Original = 'your string'
@Pattern1 = '###TaskDefId=31###'
@Replacement1 = 'STAGE CHNAGED:'

SELECT REPLACE(@String_Original,@Pattern1,@Replacement)

Now, you will have to do this repeatedly till you have replaced all the patterns that you want to replace.

Perhaps something like -

DECLARE @Replacement2 nvarchar(50)
DECLARE @Pattern2 nvarchar(50)

@String_Original = 'your string'
@Pattern2 = '###TaskDefId=46###N Enq - Send Quote'
@Replacement2 = ''

SELECT REPLACE(@String_Original,@Pattern2,@Replacement2)

And, do this for all the patterns to be replaced. But, if the pattern to be replaced is dynamic and you dont know it beforehand then I am afraid a simple, straight forward replce will not help you. You will have to write a function or stored procedure with your logic to find the patterns and replace them.

answered Dec 15, 2010 at 14:05

pavanred

14k16 gold badges56 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kashif Over a year ago

Thanks @Pavanred but I need to replace or remove ###TaskDefId=somenumber### with respect to the previous occurrence of the pattern. i.e. if previous last occurrence of the pattern is exactly same then remove it otherwise replace it.

pavanred Over a year ago

Oh then you will have to write your own logic to find, compare and replace / remove ###TaskDefId=somenumber### from your string. You will be using a lot of PATINDEX, SUBSTRING, REPLACE etc. You have some very dirty string manipulation to do. I would suggest you to find any other solution at a higher level to avoid all this. Leave such string manipulation as your last resort.

Collectives™ on Stack Overflow

Replace and remove text from string in sql server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related