Re: Convert data to title case possible?

[Jay Alverson]

Here's my shot at it, someone else might have a better/different idea...

mysql> 
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.67-community-nt | 
+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> drop table if exists titlecase;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create table titlecase (line varchar(250));
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> insert into titlecase (line) values
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE"),
    -> ("THIS IS ALL UPPERCASE TITLE");
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from titlecase;
+-----------------------------+
| line                        |
+-----------------------------+
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
| THIS IS ALL UPPERCASE TITLE | 
+-----------------------------+
15 rows in set (0.00 sec)

mysql> 
mysql> #---------------------------------------------
mysql> #  first the functions to find delimited tokens
mysql> #---------------------------------------------
mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP FUNCTION IF EXISTS `test`.`Set_GetString` $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION `test`.`Set_GetString`(str VARCHAR(500), delim VARCHAR(10), daPos SMALLINT) RETURNS varchar(500)
    -> BEGIN
    ->   DECLARE pos SMALLINT;
    ->   DECLARE dastr VARCHAR(100);
    ->   DECLARE daCount SMALLINT;
    -> 
    ->   SET daCount = 0;
    -> 
    ->   IF str = "" THEN RETURN 0; END IF;
    -> 
    -> #  drop table if exists test_getstring;
    -> #  create table test_getstring (pos smallint, dastr varchar(500), str varchar(500), dapos smallint);
    -> 
    ->   label1: LOOP
    ->     SET pos = instr(str, delim);
    ->     SET dastr = SUBSTRING_INDEX(str, delim, 1);
    ->     IF pos > 0 THEN
    ->       SET daCount = daCount + 1;
    ->       SET str = mid(str, pos+1, CHAR_LENGTH(str));
    ->       ELSE LEAVE label1;
    ->     END IF;
    ->     if str = "" THEN LEAVE label1; END IF;
    -> 
    -> #    insert into test_getstring values (pos, dastr, str, dapos);
    -> 
    ->     if daCount = daPos THEN LEAVE label1; END IF;
    ->     if daCount > 10000 then LEAVE label1; end if;
    ->   END LOOP label1;
    ->   RETURN dastr;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> ###############################################################
mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP FUNCTION IF EXISTS `test`.`Set_GetCount` $$
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION `Set_GetCount`(str VARCHAR(500), delim VARCHAR(10)) RETURNS INT
    -> BEGIN
    ->   DECLARE pos SMALLINT;
    ->   DECLARE dastr VARCHAR(100);
    ->   DECLARE daCount SMALLINT;
    -> 
    ->   SET daCount = 0;
    -> 
    ->   IF str = "" THEN RETURN 0; END IF;
    -> 
    -> #  drop table if exists test_getstring;
    -> #  create table test_getstring (pos smallint, dastr varchar(500), str varchar(500), dapos smallint);
    -> 
    ->   label1: LOOP
    ->     SET pos = instr(str, delim);
    ->     SET dastr = SUBSTRING_INDEX(str, delim, 1);
    ->     IF pos > 0 THEN
    ->       SET daCount = daCount + 1;
    ->       SET str = mid(str, pos+1, CHAR_LENGTH(str));
    ->       ELSE LEAVE label1;
    ->     END IF;
    -> 
    -> #    insert into test_getstring values (pos, dastr, str, dapos);
    -> 
    ->     if daCount > 10000 then LEAVE label1; end if;
    ->   END LOOP label1;
    ->   SET daCount = daCount + 1;
    ->   RETURN daCount;
    -> END $$
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> #---------------------------------------------
mysql> #  stored procedure to loop thru the data, find tokens
mysql> #  and convert them to the proper format...
mysql> #---------------------------------------------
mysql> 
mysql> # note size of the INPUT field...
mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP FUNCTION IF EXISTS `test`.`ConvertToTitleCase` $$
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION `test`.`ConvertToTitleCase` (fld varchar(500)) RETURNS VARCHAR(500)
    -> BEGIN
    ->   declare c,i integer;
    ->   declare thistoken, outline varchar(500);
    ->   declare thischar char;
    ->   set c := Set_GetCount(fld, " ");
    ->   set i := 1;
    ->   set outline := "";
    ->   while i <= c do
    ->     set thistoken := lcase(Set_GetString(fld, " ", i));
    ->     set thischar  := ucase(mid(thistoken, 1, 1));
    ->     set outline   := concat(outline, " ", concat(thischar, mid(thistoken, 2, 1000)));
    ->     set i := i + 1;
    ->   end while;
    ->   return(outline);
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> #---------------------------------------------
mysql> #  and finally...
mysql> #---------------------------------------------
mysql> 
mysql> select line, ConvertToTitleCase(line) as "Title Case" from titlecase;
+-----------------------------+------------------------------+
| line                        | Title Case                   |
+-----------------------------+------------------------------+
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is All Uppercase Title | 
+-----------------------------+------------------------------+
15 rows in set (0.01 sec)

mysql> 
mysql> select line, ConvertToTitleCase(replace(line, "ALL UPPER", "NOW TITLE")) as "Title Case" from titlecase;
+-----------------------------+------------------------------+
| line                        | Title Case                   |
+-----------------------------+------------------------------+
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
| THIS IS ALL UPPERCASE TITLE |  This Is Now Titlecase Title | 
+-----------------------------+------------------------------+
15 rows in set (0.00 sec)

mysql> 
mysql> notee

>

Thanks, Jay



Edited 1 time(s). Last edit at 04/17/2009 07:47AM by Jay Alverson.