sql server 2000: TSQL special characters handling..

Question

Just using SQL Server 2000 built in features ONLY, what is the best way to handle special characters. I am not sure if Regular Expression could be used by purely using built in features? I would like to search and replace the special characters in my queries.

Thanks

Answer 1

Nested replace

REPLACE(REPLACE(REPLACE(value, '$', ''), '"', ''), ':', '')

Really, this isn't something t-sql is good at

Answer 2

Although this may violate the definition of using "built-in features ONLY", since it relies on WSH, the function outlined in this posting is one way to get regexes in to SQL 2000, and could be extended to support replacement etc. While this isn't pure TSQL, it shouldn't require any new software or extensions on the server (although many DBAs would lock down the COM-scripting stored proc).

Otherwise, a gbn has mentioned, the only native TSQL operation available is a whole bunch of REPLACEs.