April 29, 2009 at 6:49 am
Is there away to find out what records are not in all CAPITAL letters? I have some data that I'm wanting to get cleaned up and make certain columns all capitals but am looking for finding an easy way to find thoe records that are not in all capital letters in a certain column.
April 29, 2009 at 7:06 am
SELECT * FROM table
WHERE field UPPER(field) COLLATE SQL_Latin1_General_CP1_CS_AS
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 29, 2009 at 7:08 am
do you have to find them or could you run a script that just updates all the rows?
update table
set col1 = upper(col1)
April 29, 2009 at 7:47 am
davidandrews13 (4/29/2009)
do you have to find them or could you run a script that just updates all the rows?update table
set col1 = upper(col1)
Depending on the number of rows you actually need to fix vs the total number of rows, that might or might not be a good solution. What if you have 10-million rows, and you only need to fix 5 of them? You're going to lock the whole table, and put a HUGE entry into the transaction log, where you could get away with a very small amount. On the other hand, if it's 1,000 rows total and you need to fix 600 of them, then, yeah, just update the whole thing, and you'll be fine.
But just having the server fix them instead of looking for them and then fixing them is the right idea. Just, depending on your circumstances, you might want to run the update script with a Where clause on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply