Replace and remove text from string in sql server

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:

1. This string is in one column of table and I need to update the same column after these changes.

2. As I have explained in my question that I don't just want to replace and remove the pattern but replace the ###TaskDefId=seo开发者_开发知识库mnumber### 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

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.