Post reply

How to make a column comply to a specific format

  • December 4, 2014 at 2:28 pm

    #397734

    Dear All

    I need to insert and update a table. One column of the table needs to conform to a format as: XXXXXXX.XXXXX

    If any data is not complying to the format an error needs to be thrown.

    Any one have any idea how to implement this constrait!

    Thank you so much!

  • December 4, 2014 at 2:47 pm

    #1762830

    with a check constraint.

    you can use a like statement to make it only alpha([A-Z], or allow alpha numeric[A-Z,0-9]

    CREATE TABLE Example(

    ExampleID int identity(1,1) not null Primary key,

    SomeText varchar(30),

    ConstrainedItem varchar(13) CHECK (ConstrainedItem LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z].[A-Z][A-Z][A-Z][A-Z]' ))

    INSERT INTO Example(SomeText,ConstrainedItem) SELECT 'Bad Data','Bananas'

    INSERT INTO Example(SomeText,ConstrainedItem) SELECT 'Good Data','abcdefg.qwer'

    INSERT INTO Example(SomeText,ConstrainedItem) SELECT 'Bad numbers','1234567.8910'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 4, 2014 at 2:47 pm

    #1762831

    Without getting into datatype questions (as to if the XXX means numeric data or not), sounds like you need a CHECK constraint:

    http://technet.microsoft.com/en-us/library/ms188258%28v=sql.105%29.aspx

  • December 5, 2014 at 12:07 pm

    #1763056

    Thank you! Lowel

    It worked!

Viewing 4 posts - 1 through 3 (of 3 total)

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