Well, I don't think it's working. I used this function:

DECLARE @allCharsAllowed int

set @allCharsAllowed = ( Select
case
when @stringToCheck like '%[^a-zA-Z0-9!@~#$\^&*()\_+-;:",.{}]%'
escape '\' then 1
else 0
end )

-- Return the result of the function
RETURN @allCharsAllowed

When I passed in [ and ' and < I got a 0 back. Good. But passing in [a
returned a 1. Maybe I need to check that the count of all characters
matching the chars in the list is the same as the length of the
string. I'm a C# programmer here, so I think in loops and SQL does
not, so I tend to think in the wrong direction.

This seems like a lot of work for a validator. Should I just switch to
a regex?

On Jun 21, 1:37 pm, "Aaron Bertrand [SQL Server MVP]"
[quoted text, click to view]

A non printing character getting into my database broke my app this
morning (well it just stopped returning results). So those are in the
list of things that I don't want to allow. 26 + 26 + 10 + 20 is 82, so
there are 174 ASCII chars that are on the "not allowed" list. It's a
larger list.

On Jun 21, 1:53 pm, "Aaron Bertrand [SQL Server MVP]"
[quoted text, click to view]

Typo. I must've cut and pasted before I finished the function.

I think I have it. I just take the LIKE statement above and walk the
chars one by one. I hope someone can improve upon this. It looks like
a lot of work for every insert/update to be doing.

-- BEGIN FUNCTION

-- Declare the return variable here
DECLARE @allCharsAllowed int
declare @substring varchar(1)
declare @position int
set @allCharsAllowed = 1
set @position = 1

-- while each char is in the allowed list, look at each character
while @allCharsAllowed <> 0 AND @position < len(@stringToCheck)
begin
-- get substring
set @substring = (substring(@stringToCheck , @position, 1))

-- check the substring
set @allCharsAllowed = ( Select
case
when @substring like '%[a-zA-Z0-9!@~#$\^&*()\_+\-;:",.\{\}]%' escape
'\' then 1
else 0
end )

-- move to next
set @position = @position + 1
end

-- returns 0 if any illegal chars are found
RETURN @allCharsAllowed

-- END

Almost. The inner loop should be:

while @allCharsAllowed <> 0 AND @position < (len(@stringToCheck) +1)
begin
-- get substring
set @substring = (substring(@stringToCheck , @position, 1))
print @substring
-- check the substring
set @allCharsAllowed = ( Select
case
when @substring like '%[a-zA-Z0-9!@~#$\^&*()\_+\-;:",.\{\}]' escape
'\' then 1
else 0
end )

-- move to next
set @position = @position + 1

end

Hi Aaron,

Good catch. I guess we should escape also the minus sign in this case.

select
case
when 'C=' like '%[^a-zA-Z0-9!@~#$\^&*()\_+\-{};:",.]%' escape '\' then 1
else 0
end
go


Thanks,
Alejandro Mesa

[quoted text, click to view]