How do I change the case of a string with SQL?

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 |*/
view raw SQL_upper_lower.sql hosted with ❤ by GitHub

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!