Question: changing case on existing records

[DW]

Greetings:

I have a SQL 2000 database, in which about 1% of the records are in
lower case. I need to make them UPPER CASE.

Is there a function to determine and change the case of existing
records, or will I have to re-write the records manually?

Thanks,

DW

[joeNOSPAM]

update mytable set mydatum = UPPER(mydatum)
where mydatum != UPPER(mydatum)

[David Rawheiser]

"jo*******@BEA.com" <jo***********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

update mytable set mydatum = UPPER(mydatum)
where mydatum != UPPER(mydatum)

You think that would work, as simple as it is, right? ... but NO!

That will only work if the database is case sensitive.

With a case insensitive collation the compaison will return true even if
they aren't

in that case add a specific COLLATE clause to insure that the comparison is
done case sensitive.

[John Bell]

Hi

Use the COLLATE clause and specify a case sensitive collation.

SELECT Col1
FROM ( SELECT 'A' AS Col1
UNION ALL SELECT 'a' ) DT
WHERE col1 = 'A'

SELECT Col1
FROM ( SELECT 'A' AS Col1
UNION ALL SELECT 'a' ) DT
WHERE col1 = 'A' COLLATE Latin1_General_CS_AI

Alternatively a more permanent option would be to change the column
collation using the ALTER TABLE statement or possibly propage changes up
through the whole system
http://tinyurl.com/5m7qv

John
"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:xF*********************@bgtnsc05-news.ops.worldnet.att.net...

"jo*******@BEA.com" <jo***********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

update mytable set mydatum = UPPER(mydatum)
where mydatum != UPPER(mydatum)

You think that would work, as simple as it is, right? ... but NO!

That will only work if the database is case sensitive.

With a case insensitive collation the compaison will return true even if
they aren't

in that case add a specific COLLATE clause to insure that the comparison
is done case sensitive.

[DW]

Well, it appears that, in my case at least, my database is
case-sensitive - the UPPER function worked fine.

Thanks!

[--CELKO--]

>> it appears that, in my case at least, my database is case-sensitive
- the UPPER function worked fine. <<

Now put a constraint on the column so you do not have to do this again.
Mop the floor, but remember to fix the leak.

[DW]

Good plan.