Remove special characters but not spaces SQL

This function works fine, but I am trying to keep spaces. Any help would be greatly appreciated

create function dbo.RemoveSpecialChars 
(@s varchar(256)) returns varchar(256)
   with schemabinding
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   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
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
   if len(@s2) = 0
      return null
   return @s2

2 answers

  • answered 2018-10-11 20:06 Lukasz Szozda

    You need to handle space character:

    if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 
       or @c = ASCII(' ')

    db<>fiddle demo

  • answered 2018-10-11 20:46 Alan Burstein

    We solved this problem years ago. What you need is a copy of PatReplace8k. It is the fastest function for this kind of this without a close second. Note the examples:

    -- Example 1: Against a variable
    DECLARE @string VARCHAR(8000) = '#$!^@#%! Blah blah blah (^&@(#&@!';
    SELECT string = @string, f.NewString
    FROM   samd.patReplace8k(@string, '[^a-zA-Z ]','') AS f
    -- Example 2: Using APPLY against a table
    DECLARE @table TABLE(somestring VARCHAR(256)); 
    INSERT  @table VALUES('ABC123 !!!! Hi'), ('&&&&&Letters here^^^^^^^^^');
    SELECT t.somestring, f.NewString
    FROM   @table AS t
    CROSS APPLY samd.patReplace8K(t.somestring,'[^a-zA-Z ]','') AS f;


    string                                       NewString
    -------------------------------------------- ----------------
    #$!^@#%! Blah blah blah (^&@(#&@!             Blah blah blah 
    somestring                                   NewString
    -------------------------------------------- ----------------
    ABC123 !!!! Hi                               ABC  Hi
    &&&&&Letters here^^^^^^^^^                   Letters here

    As a rule - you want to avoid T-SQL Scalar User-defined functions at all costs! They always perform horribly and have a number of bugs and other problems. ITVF is the only way to go - Always.