Sybase patIndex string/column validation

Recently one of my colleagues asked me if there is any way we can validate a given string or a column in a Sybase table for containing only alphanumeric characters. Asking what exactly she wanted, I came to know that she wanted a string to just contain a-z,A-Z,0-9 and no more.

There was no IsAlpha function in Sybase AFAIK. So the suggestion was to go the other way – use “patIndex” to cancel out all the special characters.

Declare @variable1 varchar(20)
Set @variable1 = "S12345[67D"
Select patindex('%[~!@#$%^&*()_+{}"?<>:/.,'';[]\`=-]%',@variable1)

Result would be 7 (the string assigned to @variable1 has a ‘[’ at position 7)

patIndex would return a value greater than 0 if any of the special characters specified is present in the passed variable or a column value. There could be a better/simpler way of doing this, but this one quickly served the current purpose of hers.

However, a better way of doing it would be:
Declare @variable1 varchar(20)
Set @variable1 = "S12345[67D"
Select patindex('%[^a-z,A-Z,0-9]%',@variable1)

Result would be greater than zero if any character other than the one specified in the range is in the variable, in our case again 7.