SQL Constraint - Check Uppercase

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

General SQL Server Forums

New to SQL Server Programming

SQL Constraint - Check Uppercase

Author

Topic

Wobeca
Starting Member

12 Posts

Posted - 2011-05-23 : 14:34:13

Hi there,

I'm looking for a way to check if the given character in a new record is in an uppercase.

I have a table for employees, with all their data (firstname, lastname, date of birth,...)
In this table, I also have a column 'gender' with data type 'char(1)'.
When filling in a record, I have a constraint that checks if the entered data is 'M','F' or NULL. This works fine. I would also like to have the case checked of this column. When I enter 'm' or 'v', it is accepted and I want it to be denied.

Anyone who can help me?
This is the constraint that I have so far:

ALTER TABLE dbo.TblEmployees
ADD CONSTRAINT Restriction_Gender CHECK (Gender ='M' OR Gender ='F' OR Gender IS NULL);

What do I need to do to let it check the (upper)case?
Should I do it in a script, or is it possible in the managment studio?

Thanks in advance!

Wobeca

latch
Yak Posting Veteran

62 Posts

Posted - 2011-05-23 : 15:11:29

Make use of UPPER or ASCII in your add constraint statement like these:

ALTER TABLE dbo.TblEmployees
ADD CONSTRAINT Restriction_Gender CHECK (Upper(Gender) ='M' OR upper(Gender) ='F' OR Gender IS NULL);

(or)

ALTER TABLE dbo.TblEmployees ADD
CONSTRAINT Restriction_Gender
CHECK (ASCII(Gender)=ASCII('M') or ASCII('F') or Gender is null);

Additional Info:

check these:
http://stackoverflow.com/questions/182379/uppercase-first-two-characters-in-a-column-in-a-db-table

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-23 : 15:18:56

Slight fix to latch's code:

ALTER TABLE dbo.TblEmployees ADD
CONSTRAINT Restriction_Gender
CHECK (ASCII(Gender) IN(ASCII('M'),ASCII('F')) or Gender is null);

Wobeca
Starting Member

12 Posts

Posted - 2011-05-23 : 15:25:03

The ASCII in the constraint did the trick!

Thanks guys, this is much appreciated!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-05-23 : 23:12:41

This way would probably be easer to code in cases where there is a long list of values to check.

ALTER TABLE dbo.TblEmployees ADD
CONSTRAINT Restriction_Gender
CHECK (Gender collate Latin1_General_BIN in ('M','F') or Gender is null);

CODO ERGO SUM

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-24 : 21:28:44

>> I'm looking for a way to check if the given character in a new record [sic] is in an uppercase. <<

Rows are not records; records cannot have a constraint like t6his.

>> I have a table for employees [sic] , with all their data (firstname, lastname, date of birth,...) <<

Tables model sets of things; the name should be a collective noun, in this case Personnel.

>> In this table, I also have a column 'gender' with data type 'CHAR(1)'. <<

The ISO Standard is called the “sex_code” and its values are 0 = unknown, 1 = male, 2 = female, 9 = lawful persons(corporations, etc)

For other situations, you can use:

CHECK (foo = UPPER(foo) )
or
CHECK (foo IN (<list of uppercase constants>) )

Watch your collations!

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources