Although rarely, it could happen that you end up with fully capitalized strings in SQL.
Whilst there’s often nothing wrong with the data quality, it’s not very appealing to be showing the users of your analyses data in capital letters. Personally, I always get the feeling of being shouted at when I see fully capitalized strings and that’s the very least tone I’d like my analysis to convey.
So, how do you go about it?
There are two super neat functions that all SQL Server, PostgreSQL and MySQL databases support to this end – lower() and upper().
| ---------------------SQL Server---------------------------------------------------------------- | |
| select | |
| title, | |
| lower(title) as lowercase_string, | |
| upper(title) as uppercase_string | |
| from datageeking.dbo.films | |
| /* | |
| title |lowercase_string |uppercase_string | | |
| ----------------------------|----------------------------|----------------------------| | |
| ACADEMY DINOSAUR |academy dinosaur |ACADEMY DINOSAUR | | |
| ACE GOLDFINGER |ace goldfinger |ACE GOLDFINGER | | |
| ADAPTATION HOLES |adaptation holes |ADAPTATION HOLES | | |
| AFFAIR PREJUDICE |affair prejudice |AFFAIR PREJUDICE | | |
| AFRICAN EGG |african egg |AFRICAN EGG | | |
| AGENT TRUMAN |agent truman |AGENT TRUMAN |*/ | |
| ---------------------MySQL----------------------------------------------------------------------- | |
| select | |
| title, | |
| lower(title) as lowercase_string, | |
| upper(title) as uppercase_string, | |
| lcase(title) as lowercase_string, | |
| ucase(title) as uppercase_string | |
| from sakila.film | |
| /* | |
| title |lowercase_string |uppercase_string | | |
| ----------------------------|----------------------------|----------------------------| | |
| ACADEMY DINOSAUR |academy dinosaur |ACADEMY DINOSAUR | | |
| ACE GOLDFINGER |ace goldfinger |ACE GOLDFINGER | | |
| ADAPTATION HOLES |adaptation holes |ADAPTATION HOLES | | |
| AFFAIR PREJUDICE |affair prejudice |AFFAIR PREJUDICE | | |
| AFRICAN EGG |african egg |AFRICAN EGG | | |
| AGENT TRUMAN |agent truman |AGENT TRUMAN |*/ | |
| ----------------------PostgreSQL-------------------------------------------------------------------- | |
| select | |
| title, | |
| lower(title), | |
| upper(title), | |
| from public.film | |
| /* | |
| title |lowercase_string |uppercase_string | | |
| ----------------------------|----------------------------|----------------------------| | |
| ACADEMY DINOSAUR |academy dinosaur |ACADEMY DINOSAUR | | |
| ACE GOLDFINGER |ace goldfinger |ACE GOLDFINGER | | |
| ADAPTATION HOLES |adaptation holes |ADAPTATION HOLES | | |
| AFFAIR PREJUDICE |affair prejudice |AFFAIR PREJUDICE | | |
| AFRICAN EGG |african egg |AFRICAN EGG | | |
| AGENT TRUMAN |agent truman |AGENT TRUMAN |*/ |
MySQL has even gone to the lengths of extending lcase() and ucase(), perhaps in an attempt to cater for the habits of both SQL Server and SAP database users
What I miss in every database I’ve worked with so far is a function analogous to proper() in Excel….
It’s not all doom an gloom, though – you could still capitalze each word in your string… But this comes at the expense of qute a bit of coding and there is no guarantee it would work for strings composed by more than 2 words
| --*********************************************************************************************************************** | |
| ------------------------SQL Server------------------------------------------------------------------------------------- | |
| ----1) Get the first letter of the string & capitalize it | |
| upper(left(ltrim(title),1)) as first_letter_first_word, | |
| ----2) Get the remaining string | |
| right(lower(ltrim(title)), len(lower(ltrim(title)))-1) as remaining_string, | |
| ----3) Find the position of the first blank space----------------- | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) as blank_position, | |
| ----4) Get the remainig firs word /without its first letter/ | |
| left(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) ) as remaing_first_word, | |
| ----5) Separate the second word from the rest of the strin-------------- | |
| right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) - | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))) as second_word, | |
| ----6) Get the first letter of the second word and capitalize it-------------- | |
| upper(left(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) - | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))), 1)) as first_letter_second_word, | |
| -----7) get the remaining second word---------------------------------------- | |
| right(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) - | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))), | |
| len(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) - | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)))) - 1) as remining_second_word, | |
| ----------------------------------------------------------------------------------------------------------------------------- | |
| ---first letter first word------ | |
| upper(left(ltrim(title),1)) + | |
| ---remaining first word-------------- | |
| left(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) ) + | |
| ---second word (with capital letter---------- | |
| concat( | |
| ---first letter to be replaces | |
| upper(left(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) - | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))), 1) ), | |
| right(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) - | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1))), | |
| len(right(right(lower(ltrim(title)), len(lower(ltrim(title)))-1), | |
| len(right(lower(ltrim(title)), len(lower(ltrim(title)))-1)) - | |
| charindex(' ', right(lower(ltrim(title)), len(lower(ltrim(title)))-1)))) - 1) | |
| ) as final_proper | |
| from datageeking.dbo.films | |
| --******************************************************************************************************************************* | |
| -------------------------MySQL-------------------------------------------------------------------------------------------------- | |
| #----1) Get the first letter of the string & capitalize it | |
| upper(left(ltrim(title),1)) as first_letter_first_word, | |
| #----2) Get the remaining string | |
| right(lower(ltrim(title)), length(lower(ltrim(title)))-1) as remaining_string, | |
| #----3) Find the position of the first blank space----------------- | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ') as blank_position, | |
| #----4) Get the remainig firs word /without its first letter/ | |
| left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ') ) as remaing_first_word, | |
| #----5) Separate the second word from the rest of the strin-------------- | |
| right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ') ) as second_word, | |
| #----6) Get the first letter of the second word and capitalize it-------------- | |
| upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')), 1)) as first_letter_second_word, | |
| #-----7) Get the remaining second word------------------------------------------- | |
| right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')), | |
| length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' '))) - 1) as remining_second_word, | |
| #--------------------------------------------------------------------------------------------------------- | |
| #---first letter first word------ | |
| Concat (upper(left(ltrim(title),1)), | |
| #---remaining first word-------------- | |
| left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')), | |
| #---first letter second word--------------------------------------------------------- | |
| upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')), 1)), | |
| #---remaining second word---------------------------------------------------------------- | |
| right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' ')), | |
| length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| instr(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), ' '))) - 1) | |
| ) as final_proper | |
| from sakila.film | |
| --*************************************************************************************************************************************** | |
| ------------------------------PostgreSQL----------------------------------------------------------------------------------------------- | |
| ----1) Get the first letter of the string & capitalize it | |
| upper(left(ltrim(title),1)) as first_letter_first_word, | |
| ----2) Get the remaining string | |
| right(lower(ltrim(title)), length(lower(ltrim(title)))-1) as remaining_string, | |
| ----3) Find the position of the first blank space----------------- | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) as blank_position, | |
| ----4) Get the remainig firs word /without its first letter/ | |
| left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) ) as remaing_first_word, | |
| ----5) Separate the second word from the rest of the strin-------------- | |
| right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) ) as second_word, | |
| ----6) Get the first letter of the second word and capitalize it-------------- | |
| upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))), 1)) as first_letter_second_word, | |
| -----7) Get the remaining second word------------------------------------------- | |
| right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))), | |
| length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)))) - 1) as remining_second_word, | |
| --------------------------------------------------------------------------------------------------------- | |
| ---first letter first word------ | |
| Concat (upper(left(ltrim(title),1)), | |
| ---remaining first word-------------- | |
| left(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))), | |
| -----first letter second word--------------------------------------------------------- | |
| upper(left(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))), 1)), | |
| ------remaining second word---------------------------------------------------------------- | |
| right(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1))), | |
| length(right(right(lower(ltrim(title)), length(lower(ltrim(title)))-1), | |
| length(right(lower(ltrim(title)), length(lower(ltrim(title)))-1)) - | |
| position(' ' in right(lower(ltrim(title)), length(lower(ltrim(title)))-1)))) - 1) | |
| ) as final_proper | |
| from public.film |
Happy SQL-ing!
