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]

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]

for xml explicit question

I've written some for xml explicit sql, now it works fine but what i want to
do now is add another set of elements at level 2.
For example:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
</root>
is what works fine, but now what i want is to have:
<root>
<thingy number="1"/>
<thingy number="2"/>
<thingy number="3"/>
<blah number="1"/>
<blah number="2"/>
</root>
when i write the for xml explicit to do this, it says that the element for
level 2 is already defined. or something like that. the way i see it is
that "thingy" and "blah" are both at level 2 and so should both have a tag
of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
wouldnt that make it appear under "thingy"... as (according to my book) tag
is effectively the nesting level?
i can write out full example code for you, but just for now, is there
anything obvious that i'm missing? its amazing how little resources are out
there, that talk about unusual xml explicit code!
thanks
Paul
The number you assign in the query isn't a level - it's a tag identifier. So
tags, 1 and 2 can be at the same level, they're just different tags. The
thing that determines the level is the parent, and you can assign the same
parent to as many tags as you like.
here's an example from the Northwind database
SELECT 1 As TAG, NULL As Parent,
ProductID AS [thingy!1!Number],
NULL AS [blah!2!Number]
FROM Products
UNION
SELECT 2 AS TAG, NULL AS Parent,
NULL,
CategoryID
FROM Categories
FOR XML EXPLICIT
As you'll see from the results, both thingy and blah are at the same level.
Hope that helps,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
http://www.microsoft.com/mspress/books/6137.asp
"Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...
> I've written some for xml explicit sql, now it works fine but what i want
to
> do now is add another set of elements at level 2.
> For example:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> </root>
> is what works fine, but now what i want is to have:
> <root>
> <thingy number="1"/>
> <thingy number="2"/>
> <thingy number="3"/>
> <blah number="1"/>
> <blah number="2"/>
> </root>
> when i write the for xml explicit to do this, it says that the element for
> level 2 is already defined. or something like that. the way i see it is
> that "thingy" and "blah" are both at level 2 and so should both have a
tag
> of 2 and a parent of 1. i could make the tag of "blah" equal to 3 but
> wouldnt that make it appear under "thingy"... as (according to my book)
tag
> is effectively the nesting level?
> i can write out full example code for you, but just for now, is there
> anything obvious that i'm missing? its amazing how little resources are
out
> there, that talk about unusual xml explicit code!
> thanks
> Paul
>
|||Thanks! I adjusted my XML accordingly and it works, beautifully.
Its amazing how simple this all is, when you get your head round it (famous
last words until my next problem, hehe). btw I found removing the for xml
explicit part and looking at the virtual (?) table is a big help on the way
to enlightenment
Thanks again
Paul
Graeme Malcolm (Content Master Ltd.) wrote:[vbcol=seagreen]
> The number you assign in the query isn't a level - it's a tag
> identifier. So tags, 1 and 2 can be at the same level, they're just
> different tags. The thing that determines the level is the parent,
> and you can assign the same parent to as many tags as you like.
> here's an example from the Northwind database
> SELECT 1 As TAG, NULL As Parent,
> ProductID AS [thingy!1!Number],
> NULL AS [blah!2!Number]
> FROM Products
> UNION
> SELECT 2 AS TAG, NULL AS Parent,
> NULL,
> CategoryID
> FROM Categories
> FOR XML EXPLICIT
> As you'll see from the results, both thingy and blah are at the same
> level.
> Hope that helps,
> Graeme
>
> "Paul" <removethisbitthenitspaulyates@.hotmail.com> wrote in message
> news:c66778$8kic9$1@.ID-141222.news.uni-berlin.de...