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

Here is function that will strip out special characters using ASCII character range. Caution: Make sure you test it and are happy with CPU usage before implementing it in a high volumne product environment.

This function modified from source-code.biz/snippets/mssql/1.htm by Christian d'Heureuse

    CREATE FUNCTION dbo.RemoveSpecialChars (@s VARCHAR(256)) 
RETURNS VARCHAR(256)
   WITH SCHEMABINDING
BEGIN
   IF @s is null
      RETURN null

   DECLARE @s2 varchar(256)
   DECLARE @l int
   DECLARE @p int


   SET @s2 = ''
   SET @l = len(@s)

   SET @p = 1

      WHILE @p <= @l
      BEGIN

            DECLARE @c int
            SET @c = ascii(substring(@s, @p, 1))
            IF @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122
            BEGIN
                  SET @s2 = @s2 + char(@c)
            END
            SET @p = @p + 1
      END


      IF LEN(@s2) = 0
      BEGIN
            RETURN null
      END     

      RETURN @s2

END