Text replace with regex in SQL Server

Question

Currently I have a SQL server column of type nvarchar(max) which has text that starts with

<span class="escape_<<digits>>"></span>

The only thing that varies in the pattern is the <<digits>> in the class name.

The common part is

<span class="myclass_ 

and the closing

</span>

Some sample values are

<span class="myclass_12"></span>
<span class="myclass_234"></span>
<span class="myclass_4546"></span>

These span text are present only at the beginning of the column. Any such matching span in the middle should not be removed or matched.

Whats the SQL Server query with regex to remove all these occurances of span?

Answer 1

SQL Server does not support regex out of the box.

If you are using SQL Server 2005 or above, you can write a CLR function for SQL Server and call that.

See this MSDN magazine article for details of how to do that (SQL Server 2005 > Regular Expressions Make Pattern Matching And Data Extraction Easier).

Answer 2

If you have values like

<span class="myclass_12"></span>
<span class="myclass_234"></span>
<span class="myclass_4546"></span>

and you want to get just the myclass_12 etc. values from it, use this query:

SELECT 
    REPLACE(REPLACE(string, '<span class="', ''), '"></span>', '') 
FROM 
    dbo.YourTable

It's not based on a Regex since SQL Server / Transact-SQL by default doesn't support regex matching. If you need and want Regex matching by all means, consider creating a SQL-CLR assembly that implements that functionality into SQL Server.