Topic: Case Sensitive Query Posted: 20 Jul 2009 at 8:59pm
Hi guys
I need to report on a field and be able to differentiate the contents between proper case and upper case. E.g.
Some data is stored as "Stellar" and other data as "STELLAR". I have a need to find those equal to "STELLAR" [in upper case] so that I can correct the case in our database... this is proving tricky!
I use if(field = "STELLAR") then true else false. But I get both records coming up as true.
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Posted: 21 Jul 2009 at 6:23am
Is the database case sensitive?
If you need to correct the data in the database, why not update all values to the correct one?
I realize that you are probably going to use the report to identify the values that need changing, but I am willing to bet that you could write a procedure to automate it. If all of the values are 1 word long, something like this should work:
update table
set field = upper(left(field, 1)) + lower(substring(field,2,1000))
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 21 Jul 2009 at 7:09am
I agree with Lockwelles approach but just in case ...
InStr () by default is case sensitive but I would still test it to verify:
instr(field,"STELLAR")>0 should return TRUE FALSE correctly for you unless other values in your field have that string somwhere in them also. If so you can add a Left function round it to just check the first 7 characters.
Thanks guys, the field in the database is not case sensitive ... but some applications using it are. I would update the whole lot to fix the few that are wrong ... but that would take a whole day!
I found an intermediate solution after my post, upper and lower case letters have different ASCII values... so I converted it to ASCII and looked for the differences in ASCII. Rather messy, but worked!
It was hopefully a one off problem as I have fixed the source of the problem as well. Thanks for the prompt response though.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
