sql wild card chars usage

[zerocode]

hi all

i am new to sql server 2000
i want a check constraint such that

the first name should start with an upper case letter and there should be no numbers in the name.

the syntax i have tried is as follows
create table
(
.
.
.
FirstName varchar(20) not null constraint check FirstName as '_[A-Z]%^[0-9]',
.
.
.
.
)

[exterminator]

So? What is the problem? Did it work? Did it not work? Did you try testing if this worked and what were those test conditions? Regards.

[Shuja Ali]

Shouldn't the As in the T-SQL be replaced with Like FirstName varchar(20) not null constraint check FirstName Like '_[A-Z]%^[0-9]'

[zerocode]

nope

some thing wrong with the like clause

Like '[A-Z]^[0-9]%'

[Shuja Ali]

nope

some thing wrong with the like clause

Like '[A-Z]^[0-9]%'
Isn't it allowing you to run the script? Or is it not giving you the desired results while inserting data?

[exterminator]

Shouldn't the As in the T-SQL be replaced with Like FirstName varchar(20) not null constraint check FirstName Like '_[A-Z]%^[0-9]'Yes. That's right. 'As' is also available but is used when you define a column derived out of a few others based on some manipulations of their values. Examples can be found here - CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp). But of course, it is not applicable in the OP's case here. He should do something like this:

CREATE TABLE TABLENAME{
//other fields
firstName varchar(20) NOT NULL
CONSTRAINT first_name CHECK
(
firstName LIKE 'the_regex_pattern'
)
//other fields, constraints etc
}If this doesnt work then probably the problem is with your regex_pattern. Try this one - '[A-Z]%[^0-9]'. But test it good, coz I haven't. Refer to the link above as well. Hope this helps. Regards.