Capitalize first letter MySQL

Question

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

User · Answer

This should work nicely   UPDATE tb Company SET CompanyIndustry    CONCAT UPPER LEFT CompanyIndustry  1    SUBSTRING CompanyIndustry  2

User · Answer

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  lt  LENGTH  str   DO        BEGIN          SET c   SUBSTRING  s  i  1             IF LOCATE  c  punct    gt  0 THEN           SET bool   1          ELSEIF bool 1 THEN           BEGIN             IF c  gt    a  AND c  lt    z  THEN                BEGIN                  SET s   CONCAT LEFT s i-1  UCASE c  SUBSTRING s i 1                     SET bool   0                 END               ELSEIF c  gt    0  AND c  lt    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

User · Answer

Uso algo simples assim     DELIMITER    DROP FUNCTION IF EXISTS  uc frist     CREATE FUNCTION  uc frist   str VARCHAR 200   RETURNS varchar 200  BEGIN     set str   lcase str       set str   CONCAT UCASE LEFT str  1   SUBSTRING str  2        set str   REPLACE str    a     A        set str   REPLACE str    b     B        set str   REPLACE str    c     C        set str   REPLACE str    d     D        set str   REPLACE str    e     E        set str   REPLACE str    f     F        set str   REPLACE str    g     G        set str   REPLACE str    h     H        set str   REPLACE str    i     I        set str   REPLACE str    j     J        set str   REPLACE str    k     K        set str   REPLACE str    l     L        set str   REPLACE str    m     M        set str   REPLACE str    n     N        set str   REPLACE str    o     O        set str   REPLACE str    p     P        set str   REPLACE str    q     Q        set str   REPLACE str    r     R        set str   REPLACE str    s     S        set str   REPLACE str    t     T        set str   REPLACE str    u     U        set str   REPLACE str    v     V        set str   REPLACE str    w     W        set str   REPLACE str    x     X        set str   REPLACE str    y     Y        set str   REPLACE str    z     Z        return  str  END    DELIMITER

User · Answer

You can use a combination of UCASE    MID   and CONCAT     SELECT CONCAT UCASE MID name 1 1   MID name 2   AS name FROM names

User · Answer

select  CONCAT UCASE LEFT  CHRIS   1   SUBSTRING lower  CHRIS   2      Above statement can be used for first letter CAPS and rest as lower case

User · Answer

CREATE A FUNCTION   CREATE DEFINER  root   localhost  FUNCTION  UC FIRST   oldWord  VARCHAR 255     RETURNS varchar 255  CHARSET utf8  RETURN CONCAT  UCASE  LEFT oldWord  1    LCASE SUBSTRING oldWord  2      USE THE FUNCTION  UPDATE tbl name SET col name   UC FIRST col name

User · Answer

http   forge mysql com tools tool php id 201  If there are more than 1 word in the column  then this will not work as shown below  The UDF mentioned above may help in such case   mysql gt  select   from names   --------------    name            --------------    john abraham     --------------  1 row in set  0 00 sec   mysql gt  SELECT CONCAT UCASE MID name 1 1   MID name 2   AS name FROM names   --------------    name            --------------    John abraham     --------------  1 row in set  0 00 sec    Or maybe this one will help     https   github com mysqludf lib mysqludf str str ucwords

User · Answer

mysql gt  SELECT schedule type AS Schedule FROM ad campaign limit 1   ----------    Schedule    ----------    ENDDATE     ----------  1 row in set  0 00 sec   mysql gt  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

User · Answer

If anyone try to capitalize the every word separate by space      CREATE FUNCTION response name VARCHAR 40   RETURNS VARCHAR 200  DETERMINISTIC BEGIN    set  m        set  c 0     set  l 1     while  c  lt   char length name -char length replace name          do       set  c    c 1        set  p   SUBSTRING INDEX name      c         set  k   substring name  l char length  p - l 1         set  l   char length  k  2        set  m   concat  m ucase left  k 1   lcase substring  k 2            end while     return trim  m    END  CREATE PROCEDURE updateNames   BEGIN   SELECT response name  AS name FROM names  END       Result    --------------    name            --------------    Abdul Karim      --------------

User · Answer

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

User · Answer

UPDATE users SET first name   CONCAT UCASE LEFT first name  1                                  LCASE SUBSTRING first name  2     last name   CONCAT UCASE LEFT last name  1                                  LCASE SUBSTRING last name  2

User · Answer

UPDATE tb Company SET CompanyIndustry   UCASE LEFT CompanyIndustry  1      SUBSTRING CompanyIndustry  2  LEN CompanyIndustry

User · Answer

This is working nicely   UPDATE state SET name   CONCAT UCASE LEFT name  1    LCASE SUBSTRING name  2

[mysql] Capitalize first letter. MySQL

Examples related to mysql

Examples related to string

Examples related to capitalize