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 to
do this by using a constraint if possible.
Thank you.
Mike
Constraints 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 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.)
[snip]