Check T-SQL string for invalid characters

Summary: Transact-SQL user-defined function (UDF) that can help you find illegal characters in user input strings.

When programming in Transact-SQL, it's often needed to verify that input strings do not contain any illegal characters. For example, you may want to make sure that an input string does not contain any single or double quotes. Here is a user-defined function (UDF) that can help you perform this check.

The UDF takes two input parameters:

• Input string that needs to be checked and
• String containing characters that are not allowed in the input string

If no illegal characters are found, it will return an empty string; otherwise, it will return a string containing all illegal characters found in the input string.

------------------------------------------------------
-- Description:  
--  Finds all character from a given set in a string.
--
-- Parameters:
--  @String
--    String to search.
--  @CharSet
--    Character set.
--
-- Returns:
--  Characters found in the string or empty string.
------------------------------------------------------
if exists
(
  select
    1
  from
    sysobjects
  where
    id = Object_ID('[dbo].[CheckChars]')
  and type= 'FN'
)
  drop function [dbo].[CheckChars]
go

-- Create function.
create function [dbo].[CheckChars]
(
  @String  as varchar(8000),
  @CharSet as varchar(255)
)
returns
  varchar(255)
as
begin
  declare @Index int
  declare @Char  varchar
  declare @Chars varchar(255)

  set @Chars = ''

  if (@String is null OR datalength(@String) = 0)
    return @Chars

  if (@CharSet is null OR datalength(@CharSet) = 0)
    return @Chars

  set @Index = 1

  while (@Index <= datalength(@CharSet))
  begin
    set @Char = Substring(@CharSet, @Index, 1)
    
    if (CharIndex(@Char, @String) > 0)
     if (CharIndex(@Char, @Chars) <= 0)
       set @Chars = @Chars + @Char
        
    set @Index = @Index + 1
  end

  return @Chars
end
go

if @@Error = 0
  grant execute on [dbo].[CheckChars] to Public
go

You can call this UDF like this:

if (dbo.CheckChars('Hello, world!', ',!') = '')
  print 'Good input.'
else
  print 'Bad input.'

Here is the source code of the UDF creation script:

• Download CheckChars.sql