Skip to content

Instantly share code, notes, and snippets.

@ateneva

ateneva/SQL_string_functions_proper_equivalent.sql

Last active August 18, 2018 10:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ateneva/74bd7ca89dc02364a29d76472367baaa to your computer and use it in GitHub Desktop.
Save ateneva/74bd7ca89dc02364a29d76472367baaa to your computer and use it in GitHub Desktop.
Download ZIP
How do I capitalize each word in a string with SQL?
Raw
SQL_string_functions_proper_equivalent.sql
--***********************************************************************************************************************
------------------------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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment