Search code examples
sql-servercustom-fieldstype-constraints

How to Setup a Constraint to Restrict Char Field to a Set of Values?

I am having a brain fart today. In SQL Server (currently using 2008), how do I setup a char field to only accept a specific series of chars (roughly eight case sensitive letters)? And I need to re-use this "custom field" in several tables.

I know I could setup another table and put a foreign key constraint on it. Is that the only way?

Solution

  • If you use the double negative construct, this only allows chars A to H

    ALTER TABLE MyTable WITH CHECK
   CONSTRAINT CK_MyTable_MyColChars CHECK (
     MyColChars COLLATE Latin1_General_BIN NOT LIKE '%[^ABCDEFGH]%'
     )

    For re-use, use a udf

    CREATE FUNCTION dbo.CheckChars (@Value varchar(100))
RETURNS bit
AS
BEGIN
   RETURN (CASE WHEN @Value NOT LIKE '%[^ABCDEFGH]%' THEN 1 ELSE 0 END)
END
GO

... CHECK (
      dbo.CheckChars (MyColChars) = 1
     )

    If you wanted A_F, semi-colon and space it'd be '%[^ABCDEF; ]%' for example