469,950 Members | 2,321 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

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

Jul 23 '05 #1
6 1228
update mytable set mydatum = UPPER(mydatum)
where mydatum != UPPER(mydatum)

Jul 23 '05 #2

"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.

Jul 23 '05 #3
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.

Jul 23 '05 #4
DW
Well, it appears that, in my case at least, my database is
case-sensitive - the UPPER function worked fine.

Thanks!

Jul 23 '05 #5
>> 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.

Jul 23 '05 #6
DW
Good plan.

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Paul Gorodyansky | last post: by
5 posts views Thread by sparks | last post: by
17 posts views Thread by (PeteCresswell) | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.