fbpx
  • Call a website designer
    01793 239 239
  • We're here Monday to Friday
    8:00am to 6:00pm (GMT)
  • Website design in
    Swindon & London

Adding a “Title Case” (TCASE) function to mySQL and MariaDB

The following MySQL function will convert a string of up to 10 words into title case. This is very useful when batch cleaning data (e.g. when importing names from other databases), so I disagree that this is purely an application level function. This function will Title Case the first 10 words it finds in a string. The remainder of the string is discarded (you have been warned!).

The SQL:

CREATE FUNCTION `ToTitleCase`(name VARCHAR(200)) RETURNS varchar(200) CHARSET latin1
RETURN TRIM(

CONCAT_WS(' ',
CONCAT(UPPER(LEFT(SUBSTRING_INDEX(name, ' ',1),1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',1),2))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',2),LENGTH(SUBSTRING_INDEX(name, ' ',1)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',2),3 + LENGTH(SUBSTRING_INDEX(name, ' ',1))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',3),LENGTH(SUBSTRING_INDEX(name, ' ',2)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',3),3 + LENGTH(SUBSTRING_INDEX(name, ' ',2))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',4),LENGTH(SUBSTRING_INDEX(name, ' ',3)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',4),3 + LENGTH(SUBSTRING_INDEX(name, ' ',3))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',5),LENGTH(SUBSTRING_INDEX(name, ' ',4)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',5),3 + LENGTH(SUBSTRING_INDEX(name, ' ',4))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',6),LENGTH(SUBSTRING_INDEX(name, ' ',5)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',6),3 + LENGTH(SUBSTRING_INDEX(name, ' ',5))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',7),LENGTH(SUBSTRING_INDEX(name, ' ',6)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',7),3 + LENGTH(SUBSTRING_INDEX(name, ' ',6))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',8),LENGTH(SUBSTRING_INDEX(name, ' ',7)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',8),3 + LENGTH(SUBSTRING_INDEX(name, ' ',7))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',9),LENGTH(SUBSTRING_INDEX(name, ' ',8)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',9),3 + LENGTH(SUBSTRING_INDEX(name, ' ',8))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',10),LENGTH(SUBSTRING_INDEX(name, ' ',9)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',10),3 + LENGTH(SUBSTRING_INDEX(name, ' ',9))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(name, ' ',11),LENGTH(SUBSTRING_INDEX(name, ' ',10)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(name, ' ',11),3 + LENGTH(SUBSTRING_INDEX(name, ' ',10)))))
));

What’s my IP address?

Your IP address is: Please copy and paste the numbers in bold above into your reply to the support request asking for your IP address.

Read More »