JIN Weijie

Detect invalid characters in sql

A customer needs to know whether the names of the users contain some invalid characters, like tab characters.

This is the code of a function which validates a string in sql:

CREATE FUNCTION IsNameValid( @Name NVARCHAR(100) )
RETURNS BIT
BEGIN
    SELECT @Name = RTRIM(ltrim(@Name))
    IF(@name IS null) OR (@Name = '')
      RETURN 1
    
    DECLARE @len AS INT
    DECLARE @index AS INT
    DECLARE @ascii AS int
    SELECT @len =  LEN(@Name),@index = 1
    WHILE(@index <= @len)
    BEGIN
      SELECT @ascii = ASCII(substring(@Name,@index,1))
      IF ((@ascii BETWEEN 65 AND 90)
         OR (@ascii BETWEEN 97 AND 122)
         OR (@ascii BETWEEN 193 AND 252)
         OR (@ascii = 32)
         OR (@ascii = 45)
         OR (@ascii = 46)
         OR (@ascii BETWEEN 48 AND 57)
         )
      begin
         SELECT @index = @index + 1
         CONTINUE
      end
      ELSE
         RETURN 0
    END
    RETURN 1
END

Usage:

--select invalid users
SELECT *
FROM [User]
WHERE dbo.IsNameValid([FirstName]) = 0 
OR dbo.IsNameValid([LastName]) = 0

posted @ Sunday, February 07, 2010 1:39 PM