Is there a simple way to convert MySQL data into Title Case?

Question

I have a MySQL table where all the data in one column was entered in UPPERCASE, but I need to convert in to Title Case, with recognition of "small words" akin to the Daring Fireball Title Case script.

I found this excellent solution for transforming strings to lowercase, but the Title Case function seems to have been left out of my version of MySQL. Is there an elegant way to do this?

score 68 · Answer 1 · edited Aug 05 '15 at 16:45

Edit

Eureka! Literally my first SQL function. No warranty offered. Back up your data before using. :)

First, define the following function:

DROP FUNCTION IF EXISTS lowerword;
SET GLOBAL  log_bin_trust_function_creators=TRUE; 
DELIMITER |
CREATE FUNCTION lowerword( str VARCHAR(128), word VARCHAR(5) )
RETURNS VARCHAR(128)
DETERMINISTIC
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE loc INT;

  SET loc = LOCATE(CONCAT(word,' '), str, 2);
  IF loc > 1 THEN
    WHILE i <= LENGTH (str) AND loc <> 0 DO
      SET str = INSERT(str,loc,LENGTH(word),LCASE(word));
      SET i = loc+LENGTH(word);
      SET loc = LOCATE(CONCAT(word,' '), str, i);
    END WHILE;
  END IF;
  RETURN str;
END;
|
DELIMITER ;

This will lower all occurrences of word in str.

Then define this modified proper function:

DROP FUNCTION IF EXISTS tcase; 
SET GLOBAL  log_bin_trust_function_creators=TRUE; 
DELIMITER | 
CREATE FUNCTION tcase( str VARCHAR(128) ) 
RETURNS VARCHAR(128)
DETERMINISTIC
BEGIN 
  DECLARE c CHAR(1); 
  DECLARE s VARCHAR(128); 
  DECLARE i INT DEFAULT 1; 
  DECLARE bool INT DEFAULT 1; 
  DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/'; 
  SET s = LCASE( str ); 
  WHILE i <= LENGTH( str ) DO
    BEGIN 
      SET c = SUBSTRING( s, i, 1 ); 
      IF LOCATE( c, punct ) > 0 THEN 
        SET bool = 1; 
      ELSEIF bool=1 THEN  
        BEGIN 
          IF c >= 'a' AND c <= 'z' THEN  
            BEGIN 
              SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1)); 
              SET bool = 0; 
            END; 
          ELSEIF c >= '0' AND c <= '9' THEN 
            SET bool = 0; 
          END IF; 
        END; 
      END IF; 
      SET i = i+1; 
    END; 
  END WHILE;

  SET s = lowerword(s, 'A');
  SET s = lowerword(s, 'An');
  SET s = lowerword(s, 'And');
  SET s = lowerword(s, 'As');
  SET s = lowerword(s, 'At');
  SET s = lowerword(s, 'But');
  SET s = lowerword(s, 'By');
  SET s = lowerword(s, 'For');
  SET s = lowerword(s, 'If');
  SET s = lowerword(s, 'In');
  SET s = lowerword(s, 'Of');
  SET s = lowerword(s, 'On');
  SET s = lowerword(s, 'Or');
  SET s = lowerword(s, 'The');
  SET s = lowerword(s, 'To');
  SET s = lowerword(s, 'Via');

  RETURN s; 
END; 
| 
DELIMITER ;

Usage

Verify it works as expected:

SELECT tcase(title) FROM table;

Use it:

UPDATE table SET title = tcase(title);

Source: http://www.artfulsoftware.com/infotree/queries.php?&bw=1070#122

Awesome work - I needed something that would handle user defined acronyms so I've generalised what you've got here. — CurtainDog, May 27 '10 at 06:20
When using this I got this error: #1305 - FUNCTION table_name.LENGTH does not exist. Am I missing something? — Kieran Andrews, Oct 27 '10 at 05:18
I used something like this in the end, but made it work with strings up to 4000 characters, added the CR character (\n) used in text columns (such as addresses) as a 'punctuation' word separator, and simplified the code to uppercase all characters when bool=1, not just a-z; mysql handles special characters and latin etc. — Peter Brand, Jul 21 '18 at 15:25

score 14 · Answer 2 · answered Feb 15 '13 at 06:05

14

umm something like this may work

UPDATE table_name SET `col_name`= CONCAT( UPPER( SUBSTRING( `col_name`, 1, 1 ) ) , LOWER( SUBSTRING( `col_name` FROM 2 ) ) );

answered Feb 15 '13 at 06:05

Anupam

1,100
2
20
31

2

The problem with this solution is just the first letter of field value will be uppercase. Other words will remain lowercase. – Gustavo Straube Jun 20 '13 at 14:40
How can you change it so it will make other words proper cased? – NuWin Mar 23 '16 at 21:03
Makes capital letter uppercase only. – Cyber Oct 06 '20 at 13:33

score 12 · Answer 3 · edited Aug 05 '15 at 16:40

If you need to throw custom acronyms and other custom capitalisation patterns into the mix I've generalised hobodave's answer:

DELIMITER |
CREATE FUNCTION replaceword( str VARCHAR(128), word VARCHAR(128) )
RETURNS VARCHAR(128)
DETERMINISTIC
BEGIN
  DECLARE loc INT;
  DECLARE punct CHAR(27) DEFAULT ' ()[]{},.-_!@;:?/''"#$%^&*<>'; 
  DECLARE lowerWord VARCHAR(128);
  DECLARE lowerStr VARCHAR(128);

  IF LENGTH(word) = 0 THEN
    RETURN str;
  END IF;
  SET lowerWord = LOWER(word);
  SET lowerStr = LOWER(str);
  SET loc = LOCATE(lowerWord, lowerStr, 1);
  WHILE loc > 0 DO
    IF loc = 1 OR LOCATE(SUBSTRING(str, loc-1, 1), punct) > 0 THEN
      IF loc+LENGTH(word) > LENGTH(str) OR LOCATE(SUBSTRING(str, loc+LENGTH(word), 1), punct) > 0 THEN
        SET str = INSERT(str,loc,LENGTH(word),word);
      END IF;
    END IF;
    SET loc = LOCATE(lowerWord, lowerStr, loc+LENGTH(word));
  END WHILE;
  RETURN str;
END;
|
DELIMITER ;

DELIMITER | 
CREATE FUNCTION tcase( str VARCHAR(128) ) 
RETURNS VARCHAR(128)
DETERMINISTIC
BEGIN 
  DECLARE c CHAR(1); 
  DECLARE s VARCHAR(128); 
  DECLARE i INT DEFAULT 1; 
  DECLARE bool INT DEFAULT 1; 
  DECLARE punct CHAR(27) DEFAULT ' ()[]{},.-_!@;:?/''"#$%^&*<>'; 

  SET s = LCASE( str ); 
  WHILE i <= LENGTH( str ) DO
    BEGIN 
      SET c = SUBSTRING( s, i, 1 ); 
      IF LOCATE( c, punct ) > 0 THEN 
        SET bool = 1; 
      ELSEIF bool=1 THEN  
        BEGIN 
          IF c >= 'a' AND c <= 'z' THEN  
            BEGIN 
              SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1)); 
              SET bool = 0; 
            END; 
          ELSEIF c >= '0' AND c <= '9' THEN 
            SET bool = 0; 
          END IF; 
        END; 
      END IF; 
      SET i = i+1; 
    END; 
  END WHILE;

  SET s = replaceword(s, 'a');
  SET s = replaceword(s, 'an');
  SET s = replaceword(s, 'and');
  SET s = replaceword(s, 'as');
  SET s = replaceword(s, 'at');
  SET s = replaceword(s, 'but');
  SET s = replaceword(s, 'by');
  SET s = replaceword(s, 'for');
  SET s = replaceword(s, 'if');
  SET s = replaceword(s, 'in');
  SET s = replaceword(s, 'n');
  SET s = replaceword(s, 'of');
  SET s = replaceword(s, 'on');
  SET s = replaceword(s, 'or');
  SET s = replaceword(s, 'the');
  SET s = replaceword(s, 'to');
  SET s = replaceword(s, 'via');

  SET s = replaceword(s, 'RSS');
  SET s = replaceword(s, 'URL');
  SET s = replaceword(s, 'PHP');
  SET s = replaceword(s, 'SQL');
  SET s = replaceword(s, 'OPML');
  SET s = replaceword(s, 'DHTML');
  SET s = replaceword(s, 'CSV');
  SET s = replaceword(s, 'iCal');
  SET s = replaceword(s, 'XML');
  SET s = replaceword(s, 'PDF');

  SET c = SUBSTRING( s, 1, 1 ); 
  IF c >= 'a' AND c <= 'z' THEN  
      SET s = CONCAT(UCASE(c),SUBSTRING(s,2)); 
  END IF; 

  RETURN s; 
END; 
| 
DELIMITER ;

Essentially it consists of a case-insensitive word replace function and a function to capitalise the first letter of every word and perform some transforms for specific words.

Hope its helpful to someone.

score 8 · Answer 4 · answered Dec 31 '15 at 14:21

8

I used something like this

UPDATE `tablename` 
SET `fieldname` =  CONCAT(UCASE(SUBSTRING(`fieldname`,1,1)),'', LCASE(SUBSTRING(`fieldname`,2,LENGTH(`fieldname`))))

Note: This will only convert the first character to uppercase. and rest of the value to lowercase.

answered Dec 31 '15 at 14:21

hriziya

1,116
1
23
41

How can I edit the query so it can make it Proper Case? – NuWin Mar 23 '16 at 21:02

score 5 · Answer 5 · answered Sep 28 '15 at 19:19

My solution for simple proper case:

CREATE FUNCTION `proper_case`(str varchar(128)) RETURNS varchar(128)
BEGIN
DECLARE n, pos INT DEFAULT 1;
DECLARE sub, proper VARCHAR(128) DEFAULT '';

if length(trim(str)) > 0 then
    WHILE pos > 0 DO
        set pos = locate(' ',trim(str),n);
        if pos = 0 then
            set sub = lower(trim(substr(trim(str),n)));
        else
            set sub = lower(trim(substr(trim(str),n,pos-n)));
        end if;

        set proper = concat_ws(' ', proper, concat(upper(left(sub,1)),substr(sub,2)));
        set n = pos + 1;
    END WHILE;
end if;

RETURN trim(proper);
END

Use it as:

SELECT proper_case("JOHN DOE");

Output:

John Doe

Another similar function - https://stackoverflow.com/a/33561905/4050261 — Adarsh Madrecha, Nov 14 '17 at 09:35

Rajaneesh · Answer 6 · 2016-08-15T09:44:57.660

This is working for me In My SQL 5.0

      DELIMITER |
       CREATE FUNCTION CamelCase(str VARCHAR(8000))
       RETURNS VARCHAR(8000) 
          BEGIN
            DECLARE result VARCHAR(8000);
            SET str = CONCAT(' ',str,' ');
            SET result = '';
            WHILE LENGTH(str) > 1 DO
               SET str = SUBSTR(str,INSTR(str,' ')+1,LENGTH(str));
               SET result = CONCAT(result,UPPER(LEFT(str,1)), LOWER(SUBSTR(str,2,INSTR(str,' ') - 1)) )  ;
               SET str = SUBSTR(str,INSTR(str,' '),LENGTH(str));  
           END WHILE;
        RETURN result;
      END 
     |
     DELIMITER ;

score 1 · Accepted Answer · answered Jul 28 '09 at 03:22

Woo! I'm not handy with SQL at all; Here's the method that worked for me:

Export the table as a text file in .sql format.
Open the file in Textmate (which I already had handy).
Select the rows with UPPERCASE data.
Choose "Convert" from the "Text" menu, and select "to Titlecase".
Find and replace each instance of:
```
INSERT INTO `Table` (`Col1`, `Col2`, `Etc`, ...) VALUES
```
with the correct lowercase values.
Import the table back into the database.
Use UPDATE table SET colname=LOWER(colname); to reset lowercase values for the columns that should be lowercase.

The reason I didn't try using Textmate before was that I couldn't figure out how to convert a single column to Title Case without ruining the other data, but this method seems to work. Thanks for your guidance and support!

Heh. Because I'm not familiar with functions/stored procedures, I guess I'd have done it exactly your way... — Gwyneth Llewelyn, Nov 27 '20 at 19:04

score 1 · Answer 8 · answered Mar 13 '15 at 06:19

This one works for me.

UPDATE `suburbs` 
SET title2 = CONCAT_WS(' ',
CONCAT(UPPER(LEFT(SUBSTRING_INDEX(title, ' ',1),1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',1),2))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',2),LENGTH(SUBSTRING_INDEX(title, ' ',1)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',2),3 + LENGTH(SUBSTRING_INDEX(title, ' ',1))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',3),LENGTH(SUBSTRING_INDEX(title, ' ',2)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',3),3 + LENGTH(SUBSTRING_INDEX(title, ' ',2))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',4),LENGTH(SUBSTRING_INDEX(title, ' ',3)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',4),3 + LENGTH(SUBSTRING_INDEX(title, ' ',3))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',5),LENGTH(SUBSTRING_INDEX(title, ' ',4)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',5),3 + LENGTH(SUBSTRING_INDEX(title, ' ',4))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',6),LENGTH(SUBSTRING_INDEX(title, ' ',5)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',6),3 + LENGTH(SUBSTRING_INDEX(title, ' ',5))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',7),LENGTH(SUBSTRING_INDEX(title, ' ',6)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',7),3 + LENGTH(SUBSTRING_INDEX(title, ' ',6))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',8),LENGTH(SUBSTRING_INDEX(title, ' ',7)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',8),3 + LENGTH(SUBSTRING_INDEX(title, ' ',7))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',9),LENGTH(SUBSTRING_INDEX(title, ' ',8)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',9),3 + LENGTH(SUBSTRING_INDEX(title, ' ',8))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',10),LENGTH(SUBSTRING_INDEX(title, ' ',9)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',10),3 + LENGTH(SUBSTRING_INDEX(title, ' ',9))))),
CONCAT(UPPER(MID(SUBSTRING_INDEX(title, ' ',11),LENGTH(SUBSTRING_INDEX(title, ' ',10)) + 2, 1)), LOWER(MID(SUBSTRING_INDEX(title, ' ',11),3 + LENGTH(SUBSTRING_INDEX(title, ' ',10))))))
WHERE 1

score 0 · Answer 9 · answered Jul 28 '09 at 02:33

0

you could do this with concat(), substring(), and length() but I can only see it working for one word. Is there a specific reason why you can't do this in your application's code, instead of mysql?

answered Jul 28 '09 at 02:33

Mark

6,254
1
32
31

score 0 · Answer 10 · answered Nov 15 '22 at 20:50

I had Customer_Name stored in upper case. Example: MUHAMMED MACINTYRE

I had to return it in title-case/proper-case. Example: Muhammed Macintyre

I used the following code in MySql.

SELECT Customer_Name,
 concat(substring(upper(Customer_Name),1,1),
        lower(substring((substring_index(Customer_Name, ' ', 1)), 2, length(Customer_Name))),
        ' ' , 
        upper(substring(substring_index(Customer_Name, ' ', -1),1,1)),
        lower(substring(substring_index(Customer_Name, ' ', -1),2,length(Customer_Name)))
        ) as Customer_Name_TitleCase
FROM customer_table ;

CHECK RESULT HERE

score -1 · Answer 11 · answered Jul 28 '09 at 02:27

The definitive case to look for such a function is in the documentation.

Unfortunately, you've got LOWER() and UPPER() functions, but no Title Case. Your best bet would be to declare your own function that splits on spaces, ignores your small words, and does an UPPER on the first character of each remaining word.

score -1 · Answer 12 · answered Jul 28 '09 at 02:28

-1

The answer is going to depend on what type of data is in the column, i.e. is it people names, place names, book titles, etc. If you are looking for a SQL solution, I would do it in multiple updates, for example:

Initial string is: "TO KILL A MOCKINGBIRD"
Convert to initial caps: "To Kill A Mockingbird"
Convert small words to lower case if they do not start the string: "To Kill a Mockingbird"

answered Jul 28 '09 at 02:28

Jamie Ide

48,427
16
81
117

1

You forgot steps 4. and 5.: 4. ??? 5. Profit. Jokes aside; it's a sensible algorithm, but what built-in MySQL functions can accomplish step 2? – rooskie Jul 28 '09 at 02:42
None, the OP is going to have to write some SQL. The points I was trying to make were 1) "Title case" is vague without knowing the meaning of the data; and 2) This is best done in multiple steps. I'm not about to spend time writing SQL without knowing the answer to point 1. hobodave's answer looks right to me but won't give the desired results if the column is full of names such as "FLANNERY O'CONNOR". – Jamie Ide Jul 28 '09 at 13:25
1

@jamie: You'll just have to make special exceptions for the Irish. :-) – hobodave Jul 28 '09 at 18:08

Is there a simple way to convert MySQL data into Title Case?

12 Answers12

Linked