Replace text inside tags in MSSQL

Does anyone know if it's possible to replace text inside tags.

For instance...

"If I had a row that contains this text, I would want this text to be outputted but I would want <hidden&g开发者_如何学Ct;This text to not be selected</hidden>"

Any ideas?

You'd need to do some form of custom parsing on the value to remove those sections.

Performance-wise, that may not be great if you do it within TSQL. SQLCLR may be something to try out for this (much better at string manipulation etc), or do that replacement outside of SQL altogether in the calling code.

As the other two said, T-SQL is not the best way to do this. If you REALLY want to though, for your example you could do something like:

DECLARE @String varchar(1000)

SET @String = 'If I had a row that contains this text, I would want this text to be outputted but I would want <hidden>This text to not be selected</hidden>'

SELECT LEFT(@string, (CHARINDEX('<Hidden>',@string)-1)) + (RIGHT(@string, ((LEN(@String) -(CHARINDEX('</Hidden>', @String))))+1-(LEN('</hidden>'))))

This should be a good motivator to NOT do it this way.

As per AdaTheDev it seems that SQLCLR is the way to go. I did a quick google on T-SQL regex and turned up an MS link that confirms it for 2005.