>>I am trying to disallow special characters in otherwise open varchar fields. I can create a function to do a replace (field,’@’,’’) on each special character for each field, but that would be cumbersome and CPU intensive, and it would change the intended input rather than giving an error message. SQL string functions are limited. I am looking for the best way to do this. Any ideas??
>
>You can use a trigger or CHECK Constraint.

The function would allow the code to be reused over several tables or even databases. It could be used in triggers.

CREATE FUNCTION HasSpecChar (@StringToTest VARCHAR(5000))
RETURNS BIT AS
BEGIN
DECLARE @ReturnBit BIT
SET @ReturnBit =
CASE WHEN '~' IN (@StringToTest) THEN 1
WHEN '!' IN (@StringToTest) THEN 1
.
.
.
WHEN '?' IN (@StringToTest) THEN 1
WHEN '/' IN (@StringToTest) THEN 1
ELSE 0
END

Return(@ReturnBit)
END

In a trigger or a CHECK Constraint there is still the issue of having to check each field for each special character. Can you think of a better, quicker way of doing this, or is this as good as another choice?