Post reply

Check Constraint to enforce a pattern within data

  • October 18, 2006 at 5:40 am

    #114708

    I was wondering if it is possible to have an check constraint enforce a specific pattern in the data , including the case.

    for example:  ([CustomerID]like'[A-Z][a-z][a-z][0-9]')

    I thought it would make sure the first character was a upper A-Z, the second lower a-z, third lower a-z, and the forth 0-9.

    the expression excepts any combo of characters upper or lower in the first three character positions.

    I've tried making the column as a case sensitive collation but that did not work either.

    Does any one know of an expression that will work in that example?

     

  • October 18, 2006 at 6:33 am

    #666457

    harold

    I know a slightly different way to do this.

    i dont know whthr we could straightway put a expression that u want in a check constraint, but u cud create a function and add that function to the check constraint. The function can check for whatever expression that needs to be checked. Doing like this u can use T-Sql which i think would be more flexible.

    Anyways keep us posted with your progress

    "Keep Trying"

  • October 18, 2006 at 7:01 am

    #666468

    Thanks I was wondering about the expression but the function idea is good. What would the sentax be to add it the the constraint?

  • October 18, 2006 at 7:26 am

    #666486

    [CustomerID] LIKE '[A-Z][a-z][a-z][0-9]'

    AND ASCII(SUBSTRING([CustomerID],1,1)) = ASCII(UPPER(SUBSTRING([CustomerID],1,1)))

    AND ASCII(SUBSTRING([CustomerID],2,1)) = ASCII(LOWER(SUBSTRING([CustomerID],2,1)))

    AND ASCII(SUBSTRING([CustomerID],3,1)) = ASCII(LOWER(SUBSTRING([CustomerID],3,1)))

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 19, 2006 at 12:52 am

    #666713

    harold

    the syntax for adding a function to a check constraint

    ALTER TABLE CheckTbl

    ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );

    i got it from BOL... go there for more info

    "Keep Trying"

  • October 19, 2006 at 5:23 am

    #666753

    Thanks Chiraq and Both options work fine.

Viewing 6 posts - 1 through 5 (of 5 total)

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