Force data to lowercase in a field in sql server 2000

How do you force data to lowercase for a field? For example, I would like t
o
do this by using a constraint if possible.
Thank you.
--
MikeConstraints do not change data but rather they enforce some form of
validation. If you don't want to apply this data transformation in your
client applications, then you could use a trigger that performs a LOWER() to
the designated field.
--Brian
(Please reply to the newsgroups only.)
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:73C99281-0C82-45D6-AC29-73FDC1307E9A@.microsoft.com...
> How do you force data to lowercase for a field? For example, I would like
> to
> do this by using a constraint if possible.
> Thank you.
> --
> Mike|||Thanks for the feedback. I was hoping to create some type of constraint
where only lowercase letters could be entered into a field.
Thank you.
Mike
"Mike" wrote:

> How do you force data to lowercase for a field? For example, I would like
to
> do this by using a constraint if possible.
> Thank you.
> --
> Mike|||That you can do, meaning that if the data is not already lowercase, then
reject the insert/update. I was thinking that you wanted to have the table
definition force data to lower case. Anyway, table DDL with the CHECK
constraint might look something like:
create table tab1
( col1 varchar(10) COLLATE Latin1_General_BIN CONSTRAINT
force_lowercase_ck CHECK (col1 = lower(col1)))
The key here is to explicitly define the col1 column as case-sensitive then
combine that with the check constraint to reject invalid formatting.
--Brian
(Please reply to the newsgroups only.)
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:932C1B12-E1C7-4AF9-AFE1-A94A5FDFE311@.microsoft.com...[vbcol=seagreen]
> Thanks for the feedback. I was hoping to create some type of constraint
> where only lowercase letters could be entered into a field.
> Thank you.
> --
> Mike
>
> "Mike" wrote:
>|||Thank you! That's exactly what I needed.
--
Mike
"Mike" wrote:

> How do you force data to lowercase for a field? For example, I would like
to
> do this by using a constraint if possible.
> Thank you.
> --
> Mike|||Mike,
You don't have to change the column's collation (thus avoiding side
effects when sorting, etc.). You can limit the collation change to the
check constraint. See below:
create table tab1
( col1 varchar(10) CONSTRAINT
force_lowercase_ck CHECK (col1 = lower(col1) COLLATE
Latin1_General_BIN))
Gert-Jan
Brian Lawton wrote:
> That you can do, meaning that if the data is not already lowercase, then
> reject the insert/update. I was thinking that you wanted to have the tabl
e
> definition force data to lower case. Anyway, table DDL with the CHECK
> constraint might look something like:
> create table tab1
> ( col1 varchar(10) COLLATE Latin1_General_BIN CONSTRAINT
> force_lowercase_ck CHECK (col1 = lower(col1)))
> The key here is to explicitly define the col1 column as case-sensitive the
n
> combine that with the check constraint to reject invalid formatting.
> --
> --Brian
> (Please reply to the newsgroups only.)
[snip]