SQL Server: Replacing all special chars in SQL string

Question

I am working with SQL Server 2005. I need to select all rows from a table that have the same string as a given parameter.

SELECT * FROM mytable WHERE title LIKE @param;

The only problem is that the title column may contain special chars that have already been replaced by '-' in the compared @param.

Any char in @param except [a-z][A-Z][0-9][-] got replaced by '-', so there is no way to reverse this. (the value comes from a SEO friendly url)

Examples for possible title values and how they should look like:

"Adobe Acrobat" -> "Adobe-Acrobat"  
"A+® Certification" -> "A-Certification"

I cannot use the SQL REPLACE() function as I need a white-listing mechanism for the chars. There might be very special chars in the title column which I cannot predict.

Regular Expressions would be perfect for this.

I searched for 2 days now but couldnt find an easy and efficient way to implement it in T-SQL.

I could get all rows and do the filtering in the client code, but that doesn't seem to be a very good solution to me.

Any suggestions?

Answer 1

If you are using SQL Server 2005 and above, you can use SQL CLR functions to use regex efficiently within SQL Server.

There is a download available for a RegEx SQL CLR here, with source code.