Post reply

Check Constraint with Case Statement

  • August 5, 2010 at 7:12 pm

    #225804

    I need to check the column and if the value entered is 1 digit, I need to make it two digits like 01. If the value is two digit already, just leave it alone.

    I am using check constraint to accomplish this. But this is not working..Any help would be appreciated.

    ALTER TABLE [dbo].TableName WITH CHECK

    ADD CONSTRAINT [CheckColLength] CHECK (case when len(col) < 2 THEN '0' + col else col end=1))

    GO

    Can we use Case statement in check constraint?

    Thanks for your help.

  • August 6, 2010 at 2:58 am

    #1203308

    Hi

    You can't use a check constraint to alter values - it can only check the values and generate an error if they are incorrect.

    You can however use a trigger to do what you want to do. You'd want a FOR UPDATE, INSERT trigger which checks the column value and changes it if necessary. Have a look at trigger syntax and get back if you have any problems.

    There may of course be other ways of doing this, so let's see what else people come up with!

    Duncan

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply