February 15, 2019 at 8:51 am
Hi everyone
I'm using the following script to capitalise the values of our COUNTRY field via a Stored Procedure. It's clearly not very efficient and also open to error (what if I forget about a country for example?!)
update CONTACT set COUNTRY='LUXEMBOURG' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Luxembourg' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MACAO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Macao' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MALAWI' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Malawi' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MALAYSIA' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Malaysia' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MALTA' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Malta' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MAURITIUS' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Mauritius' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MEXICO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Mexico' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MONACO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Monaco' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MOROCCO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Morocco' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MYANMAR' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Myanmar' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='NAMIBIA' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Namibia' COLLATE Latin1_General_Bin)
Is there a quicker way to capitalise the contents of the COUNTRY field, maybe using only one line of script?
Many thanks
Jon
February 15, 2019 at 8:55 am
j.clay 47557 - Friday, February 15, 2019 8:51 AMHi everyone
I'm using the following script to capitalise the values of our COUNTRY field via a Stored Procedure. It's clearly not very efficient and also open to error (what if I forget about a country for example?!)update CONTACT set COUNTRY='LUXEMBOURG' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Luxembourg' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MACAO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Macao' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MALAWI' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Malawi' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MALAYSIA' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Malaysia' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MALTA' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Malta' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MAURITIUS' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Mauritius' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MEXICO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Mexico' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MONACO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Monaco' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MOROCCO' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Morocco' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='MYANMAR' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Myanmar' COLLATE Latin1_General_Bin)
update CONTACT set COUNTRY='NAMIBIA' WHERE (COUNTRY COLLATE Latin1_General_Bin ='Namibia' COLLATE Latin1_General_Bin)Is there a quicker way to capitalise the contents of the COUNTRY field, maybe using only one line of script?
Many thanks
Jon
UPDATE C
SET C.COUNTRY = UPPER(C.COUNTRY)
FROM CONTACT AS C
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2019 at 9:04 am
You might want to test for lower case chars before doing the UPDATE, using any case-sensitive collation:
UPDATE CONTACT
SET COUNTRY = UPPER(COUNTRY)
WHERE COUNTRY COLLATE Latin1_General_Bin LIKE '%[a-z]%'
Better yet, add a trigger on the table that does that automatically when any row is INSERTed or UPDATEd.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 15, 2019 at 9:34 am
Thank you both, fantastic!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply