Hello,
suppose I want to replace all instances of “King” and “king” into “queen”. Since, as far as I know, SQLlite’s replace function is always case-sensitive, I write:
update kLibrary set vGenre = replace(vGenre, ‘king’ , ‘queen’) where vGenre = ‘king’
update kLibrary set vGenre = replace(vGenre, ‘King’ , ‘queen’) where vGenre = ‘king’
May be the answer to my question is obvious, yet I’d like to know if is there any way to accomplish the replace in one go, i.e. as if the replace function were non case-sensitive, and have all instances of “King” and “king” replaced by “queen”.
I knew it would
Its not the replace that is an issue since you want to replace ANY version of ‘king’ (king, King, kIng, KiNg, etc etc) with a constant replacement value
I assume you want to do this so your values in the column are consistent ?
This is one of those spots where NOT using a textual value - and using a lookup table for “the value” is useful since you change it once and all “values” now are the same
Since I have no idea about what you are doing let me offer an example
Suppose you have a table of “people” and then a table of “contact_info”
This way one person might have an email & phone, another just a fax and phone etc
You might model this as
Person
id
first_name
last_name
Contact_info
id
personId_fk
means <<<<< this might be "fax" "phone" "cell" "email etc
value
Now you might end up with the MEANS to contact a person stored in many different ways as “fax”, “Fax”, FAX etc so either you always write queries that use LIKE OR you dont use the strings in the table at all
So if you dumped out a list you might end up with (right from sql - I’m sure if you printed it from a program you’d normalize case - but you shouldn’t have to)
Person
Norman Palardy
e-mail foo@bar.com
phone 1-234-567-8910
Bob Keeney
EMAIL foo@bar.com
Phone 1-234-567-8910
Instead if you make the db like
Person
id
first_name
last_name
Contact_info
id
personId_fk
contact_method_id_fk <<<<< this is an ID from the contact methods table so you NEVER have to spell it out again
value
Contact_methods
id
name
since you only store the contact methods name (fax, cell phone, email, etc) in ONE and only one spot ANY change to the name of the means appears to affect EVERY last one all at once
UPDATE kLibrary SET vGenre = ‘queen’ WHERE UPPER(vGenre) = ‘KING’;[/quote]
SOME DB’s (like postgres) have two forms - so you have to watch for that
It has LIKE and ILIKE where ILIKE is case insensitive
Dont think that follows the SQL standard though but then there are lots of little niggly bits in most db’s that aren’t quite standard
[quote=240886:@Carlo Rubini]@Eli
// Will change ‘King’, ‘king’, ‘KING’, etc.
yet I still get “king” and “King”[/quote]
I’m very sorry I didn’t read your original post properly and gave you a MySQL answer…
This should do:
UPDATE kLibrary SET vGenre = 'queen' WHERE vGenre = 'king' COLLATE NOCASE;
@Norman
yes, I want to have consistent values in a column, and in fact I have something equivalent to “contact_method_id_fk” in your example.
So the user should first of all compose a list, and then select the relevant word he wants to enter in a record.
At the same time, I let users type words of their own choice in the record itself, and these new words, when not yet part of “contact_method_id_fk”, get automatically added to it.
The problem was that I did not implement a right way to check for already existing words (something like: if a <> b then add_the_word), so “contact_method_id_fk” got doubles like “King” and “king”.
Now I do a strComp and add only new words that pass the test.
Sorry I messed up my answer:
The problem was that I did not implement a right way to check for already existing words (something like: if a <> b then add_the_word), so “contact_method_id_fk” got doubles like “King” and “king”.
the end part should read:
so if the user added “King” and “contact_method_id_fk” already had “king”, the list would NOT get adjurned; yet the records would contain both “King” and “king”.
As I said, strComp removed this problem since both dictions are now added to the list. And so the user, looking at the list, is made aware of a double value, and if necessary makes corrections.
