August 14, 2003 at 9:40 am
Our databases are currently on the default 1252 character set, but we deal with clients who do not want to receive any special characters other than the regular US english chars. but could still transmit those special characters to us. So we need to find a way to be able to receive them and filter any special characters out before transmitting them back to the clients or the web. What would be the best workaround in this scenario ?
Also we are currently taking care of this problem by creating CHECK constraints on the name fields to check for any special characters. But this requires creating separate constraints for each letter in the varchar name fields which is like 50 + constraints. Is there any other better way to do this ?
Thanks
August 14, 2003 at 5:40 pm
Your could create a UDF that could be called from a CHECK constraint to prevent unwanted characters being entered. Or, it could be used by an INSERT/UPDATE trigger to remove unwanted characters.
An example UDF that removes anything greater than ASCII 127 is:
CREATE FUNCTION RemoveSpecialChars
(@StrIn VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @StrOut VARCHAR(1000), @i INT SET @StrOut = @StrIn
SET @i = LEN(@StrOut)
WHILE @i > 0 BEGIN
IF ASCII(SUBSTRING(@StrOut, @i, 1)) > 127
SET @StrOut = STUFF(@StrOut, @i, 1, '')
SET @i = @i - 1
END
RETURN @StrOut
END
GO
This can be called by a CHECK constraint with something along the lines of:
CREATE TABLE x
(x VARCHAR (2000) CHECK (x = dbo.RemoveSpecialChars(x)))
Cheers,
- Mark
Cheers,
- Mark
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply