SQL Server: Replacing all special chars in SQL string

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 coul开发者_如何学JAVAdnt 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?

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.

You're basically using - (dash) as a wildcard for any character. For SQL's like statement, the wildcard for any character is _ (underscore). So perhaps you could try to replace dash with underscore. For example:

where 'A+® Certification' like replace('A---Certification','-','_')

One tiny difference is that underscore matches A-Z0-9 while your dash does not.