To define Check Constraints (Format Data Type)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

To define Check Constraints (Format Data Type)

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-19 : 05:42:43

I’ m trying to define specific data types with Check Constraints. I want to define a column so that only be able to enter a code consisting in eight numbers and one letter in capital, for instance: ‘25814712X’. I’ ve obtained successfully result except the letter that accept small letters, and I only want to accept capitals. The syntax is as follows:

ALTER TABLE Users
ADD CONSTRAINT CK_code
CHECK (User_code LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]')

How can I write it so that only be able to accept letters in capitals?

How can I write a Check Constraint that only allows a characters set which contains the character ‘@’ between that, and finishes with ‘.com’, ‘.net’, ‘.uk’, ‘.fr’, ‘.org’, etc,..? I want user only be able to write the correct e-mail format

.

Thanks,
Cesar

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-09-19 : 07:11:54

a duplicate.....

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-19 : 07:51:11

I' m sorry, but nobody knows about this, SEEMS INCREDIBLE!! No policies exists in the data bases, no filter? The policies depends totally of the applications? I hope no...

If the data are not entered in the right format, when you try to do reports or print important documents you may have corrupted data, it will not be coherent format. Isn' t it?

Cesar

adweigert
Starting Member

22 Posts

Posted - 2003-09-19 : 08:29:28

by default the database collation is set to case insensitive ... there is a collation setting for case sensitivity ... and of course, check out BOL for more info ...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-19 : 08:42:25

cesar --

for the email addresses, I know no one does this, but I might consider storing them in two columns: one for the address, one for the domain. You can always concatenate them together with a "@" in the middle in views or as a computed column, and it makes validations much easier. Plus, now domain is an indexable, quickly sortable column w/o using SUBSTRING to find the location of the @. just an idea.

you could even break out the domain name verses the suffix (or whatever the ".com" part is called) and create a table of valid "com","edu", etc entries and create relationship between the suffix part of the domain and this table to allow only valid entries.

for the "user code" constraint, try:

user_code like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-z]' AND
ASCII(RIGHT(user_code,1)) between 65 and 90

- Jeff

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-19 : 09:45:08

quote:

user_code like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-z]' AND
ASCII(RIGHT(user_code,1)) between 65 and 90

That's well played. I was thinking about his question this morning then I got distracted. Good solution.

__________________
Make love not war!

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-19 : 10:47:56

Thank you Jeff.
I don’ t understand the second part of the ‘user_code’ constraint: ‘AND
ASCII(RIGHT(user_code,1)) between 65 and 90’. Anyway I’ ve tried to do writing the first part ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-z]' (only changing [A-Z] to [a-z]) and the result is the same, it allows small letters and capitals.

Refers to e-mail address, I think that I am not going to complicate the field like this. Anyway I continue thinking that is very strange that applications filter the e-mail address when you validate the form, and if the format is not correct they detect the part of the mistake. In a database seems that the same thing could be done in one column, instead of to wait or to trust with the application response. (I’ m not still prepared to do something like you have proposed

).

Thanks,
Cesar

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-19 : 11:26:27

quote:
I don’ t understand the second part of the ‘user_code’ constraint: ‘AND
ASCII(RIGHT(user_code,1)) between 65 and 90’.

that second part is the whole point ! it allows only uppercase characters ! try it out!

the second half (the key part you ignored ) is saying:

"take the RIGHTMOST character and make sure the ASCII value of that character is between 65 and 90".

the ASCII value of capital A is 65, of capital Z is 90. lowercase letters have different ASCII values and will not fall in that range, so the CHECK constraint will fail.

I hope this makes sense .

EDIT:
this shows that it works:


create table tmp (code varchar(9) 
  check (code like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-z]' 
         and ascii(right(code,1)) between 65 and 90)
)

go

-- these are allowed:
insert into tmp (code) values ('12345678A')
insert into tmp (code) values ('10022028G')
insert into tmp (code) values ('00000000Z')
insert into tmp (code) values ('99999999A')

-- these all fail:
insert into tmp (code) values ('123a5678A')
insert into tmp (code) values ('12315678a')
insert into tmp (code) values ('123456780')
insert into tmp (code) values ('122A')
insert into tmp (code) values ('12234343g')

go

select * from tmp

go
drop table tmp

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-19 : 12:25:02

Ok, now works good but I need characters like ‘Ñ’ that not allowed for this interval ascii. Does exist others intervals of values like ascii which allows other characters?

Cesar

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-19 : 14:39:21

After runnig this:

declare @i int;

set @i = 1
while @i <= 255 begin
print convert(varchar,@i) + ' - ' + char(@i)
set @i = @i +1
end

it looks like you also may want to include the range from 192-221 as well but you will need to decide for yourself. take a look at the output from the above.

look up ASCII on google for information.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-19 : 15:50:10

Another way
len(user_code) = 9 and patindex('%[^0-9]%',left(user_code,8)) = 0 and right(user_code,1) like '[A-Z] COLLATE SQL_Latin1_General_CP1_CS_AS

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-20 : 12:03:43

Ok, thank you VM. I am going to see your answers with more detail later, now is SATURDAY and I am going to Barcelona to do something more relaxed :-).

Cesar

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-22 : 10:36:00

I am trying to add the 'Ñ' character in your interval (between 65 and 90), and I can not. 'Ñ' Character in ASCII language is '165' number. How can I add the 'Ñ' character in this syntax?:

ALTER TABLE Users
ADD CONSTRAINT CK_Code
CHECK (User_code LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-z]'
and ascii(right(User_code,1)) between 65 and 90)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 10:55:26

let's break out the CHECK constraint:

the first half you know:

User_code LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-z]'

then, we added another condition, and said it must ALSO be true in addition to the first condition, so we joined them with an AND:

AND

and then we said the Rightmost character must have an ASCII value between 65 and 90, so we take the ASCII value of the rightmost character:

ascii(right(User_code,1))

and we said it must be between 65 and 90:

between 65 and 90)

Now, we want to amend that and not only allow for between 65 and 90, but also for 165. Thus, we want to allow only values between 65 and 165 OR values that are equal to 165.

Thus, our final statement:

CHECK (User_code
LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][a-z]'
and
(ascii(right(User_code,1)) between 65 and 90
OR
ascii(right(user_code,1)) = 165))

So, again, we have 3 conditions:

Condition A: that the format is 8 numbers followed by a letter
Condition B: the rightmost character has an ASCII value between 65 and 90
Condition C: the right most character has an ASCII value of 165

Now, we don't need all 3 to be true, but rather:

A must be true AND
either B or C must be true

we can't write it like this:

ConditionA AND ConditionB OR COnditionC

because it might be intrepreted as this:

(ConditionA AND ConditionB) or (ConditionC) <-- this is wrong!

so we put parenthesis around the OR to make sure it evaluates the way we want:

ConditionA AND (ConditionB or ConditionC)

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 11:37:36

please reply if this helps becuase i put a lot of work into that answer !

I am trying to get you to understand WHY and not just HOW ...

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-22 : 11:39:59

In Query Analizer the syntax is ok, but do not works when I try insert in table 'Ñ' character. The syntax is good and the '165' code that refering to 'Ñ' character too,..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 11:47:50

Why do you think the N character is 165?

It's 209.


select char(165)
select char(209)

Final note: you can take out the [a-z] part of the LIKE clause as well, especially once you start adding extra characters, since the ASCII() check takes care of them. i.e., replace [a-z] with % or _ in the like clause.

Chances are, many non A-Z characters might not pass the [a-z] condition in the LIKE clause. (please read carefully all of the information I gave you, especially where I break down the CHECK constraint one by one for you ....)

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-22 : 12:17:10

I have checked the ASCII codes in that url:http://www.asciitable.com/
Would you mind give me the original table? (with true values)

Now the inserts with this character works good!

All of this is new to me. Where can I find more information and examples about the syntax of Check Constraints? I have not found anything.

Thank you,
Cesar

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 12:25:21

I have already given you the table:


declare @i int;

set @i = 1
while @i <= 255 begin
print convert(varchar,@i) + ' - ' + char(@i)
set @i = @i +1
end

run that in Query analzyer and look at the results.

ALl of this has nothing to do with CHECK() constraints, it's all boolean logic.

- Jeff

cesark
Posting Yak Master

215 Posts

Posted - 2003-09-22 : 12:47:38

Ok, thank you

.
I am going to continu my data base design

Cesar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources