Remove special characters but not spaces SQL

Question

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
begin
   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
      end
   if len(@s2) = 0
      return null
   return @s2
   end

Re: `set @l = len(@s)` From [`Len()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql): "Returns the number of characters of the specified string expression, **excluding trailing blanks**." [`DataLength`](https://learn.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql) does not exclude trailing blanks. For Unicode strings you can use `DataLength( UnicodeStringExpression ) / DataLength( N'#' )` to get the length in characters. In general `DataLength( Left( Coalesce( StringExpression, '#' ), 1 ) )` will return the number of bytes per character. — HABO, Oct 12 '18 at 00:54
Not as fast as other approaches, but very mighty in dealing with [*re-coding* of accented characters to plain latin](https://stackoverflow.com/a/32048968/5089204). You might have to adapt this approach for a given collation... — Shnugo, Oct 12 '18 at 07:34

score 1 · Accepted Answer · answered Oct 11 '18 at 20:06

1

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 Oct 11 '18 at 20:06

Lukasz Szozda

162,964
23
234
275

score 0 · Answer 2 · answered Oct 11 '18 at 20:46

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;

Results:

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.

Remove special characters but not spaces SQL

2 Answers2