Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
create constraint to add only alphabet in column

Answered create constraint to add only alphabet in column

  • Monday, August 21, 2006 2:56 AM
     
     
    Sign In to Vote
    0

    I need to create constraint in column to add only alphabet .

    like "adc" ,"sdfsd" and not "1234adfd".plz reply soon.

     

     

All Replies

  • Monday, August 21, 2006 3:28 AM
     
     Answered
    Sign In to Vote
    0

    hi,

     check this out.

    create table mytable
    (
    mytext char(100)
    )
    GO

    ALTER TABLE MYTABLE
    ADD
    CONSTRAINT column_nonumeric_chk
    CHECK
    (
    mytext not like '%[0-9]%'
    )

    go
    insert mytable(mytext) values('hello') -- suceed
    insert mytable(mytext) values('hello2') --- will fail
    select * from mytable

    regards,

    joey

     
  • Tuesday, August 22, 2006 7:53 PM
     
     Answered
    Sign In to Vote
    0
    A better alternative would be to check only for the valid set of characters. This is typically more robust. For example, the check like '%[0-9]%' will allow other characters like ', _, " or extended characters. So do instead:
     
    mytext like '[abcedefghijklmnopqrstuvwxyzABCEDEFGHIJKLMNOPQRSTUVWXYZ]'
     
    Note that the above way of writing the LIKE pattern is also safer than '[a-zA-Z]' because the latter expression is collation dependent.