Is there a simple way to convert MySQL data into Title Case?
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?
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
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 ) ) );
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.
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