Capitalize first letter. MySQL

Does any one know the equivalent to this TSQL in MySQL parlance?

I am trying to capitalize the first letter of each entry.

UPDATE tb_Company SET CompanyIndustry = UPPER(LEFT(CompanyIndustry, 1))
+ SUBSTRING(CompanyIndustry, 2, LEN(CompanyIndustry))

Solution 1:

It's almost the same, you just have to change to use the CONCAT() function instead of the + operator :

UPDATE tb_Company
SET CompanyIndustry = CONCAT(UCASE(LEFT(CompanyIndustry, 1)), 
                             SUBSTRING(CompanyIndustry, 2));

This would turn hello to Hello, wOrLd to WOrLd, BLABLA to BLABLA, etc. If you want to upper-case the first letter and lower-case the other, you just have to use LCASE function :

UPDATE tb_Company
SET CompanyIndustry = CONCAT(UCASE(LEFT(CompanyIndustry, 1)), 
                             LCASE(SUBSTRING(CompanyIndustry, 2)));

Note that UPPER and UCASE do the same thing.

Solution 2:

Vincents excellent answer for Uppercase First Letter works great for the first letter only capitalization of an entire column string..

BUT what if you want to Uppercase the First Letter of EVERY word in the strings of a table column?

eg: "Abbeville High School"

I hadn't found an answer to this in Stackoverflow. I had to cobble together a few answers I found in Google to provide a solid solution to the above example. Its not a native function but a user created function which MySQL version 5+ allows.

If you have Super/Admin user status on MySQL or have a local mysql installation on your own computer you can create a FUNCTION (like a stored procedure) which sits in your database and can be used in all future SQL query on any part of the db.

The function I created allows me to use this new function I called "UC_Words" just like the built in native functions of MySQL so that I can update a complete column like this:

UPDATE Table_name
SET column_name = UC_Words(column_name) 

To insert the function code, I changed the MySQL standard delimiter(;) whilst creating the function, and then reset it back to normal after the function creation script. I also personally wanted the output to be in UTF8 CHARSET too.

Function creation =

DELIMITER ||  

CREATE FUNCTION `UC_Words`( str VARCHAR(255) ) RETURNS VARCHAR(255) CHARSET utf8 DETERMINISTIC  
BEGIN  
  DECLARE c CHAR(1);  
  DECLARE s VARCHAR(255);  
  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;  
  RETURN s;  
END ||  

DELIMITER ; 

This works a treat outputting Uppercase first letters on multiple words within a string.

Assuming your MySQL login username has sufficient privileges - if not, and you cant set up a temporary DB on your personal machine to convert your tables, then ask your shared hosting provider if they will set this function for you.

Solution 3:

You can use a combination of UCASE(), MID() and CONCAT():

SELECT CONCAT(UCASE(MID(name,1,1)),MID(name,2)) AS name FROM names;

Solution 4:

mysql> SELECT schedule_type AS Schedule FROM ad_campaign limit 1;
+----------+
| Schedule |
+----------+
| ENDDATE  |
+----------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT(UCASE(MID(schedule_type,1,1)),LCASE(MID(schedule_type,2))) AS Schedule FROM ad_campaign limit 1;
+----------+
| Schedule |
+----------+
| Enddate  |
+----------+
1 row in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_mid