create constraint to add only alphabet in column

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

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

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

|||

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.