The title of the first letter. MySQL

Question

Does anyone know the equivalent of this TSQL in MySQL?

I try to make up the first letter of each entry.

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

Answer 1

This is almost the same, you just need to switch to using 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 the upper case of the first letter and the lower case of the other, you just need to use the LCASE function:

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

Note that UPPER and UCASE do the same.

Answer 2

Vincents' excellent answer for Uppercase First Letter is great for capitalizing only the first letter of an entire column row.

BUT, what if you want to capitalize the first letter of each word in the rows of a table column?

For example: Abbeville High School

I did not find an answer to this in Stackoverflow. I had to put together a few answers that I found on Google to find a reliable solution for the above example. This is not a built-in function, but a user-created function that MySQL 5+ allows.

If you have Super / Admin user status in MySQL or you have a local version of mysql installed on your computer, you can create a function (for example, a stored procedure) that will be stored in your database and used in all future SQL queries in any parts db.

The function I created allows me to use this new function, which I called "UC_Words", just like the MySQL built-in built-in functions, so that I can update the full column as follows:

 UPDATE Table_name SET column_name = UC_Words(column_name) 

To insert the function code, I changed the standard MySQL separator (;) when creating the function, and then returned it to its normal state after the function creation script. I also personally wanted the output to be in UTF8 CHARSET.

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 by capitalizing a few words on a line.

Assuming that your username for logging into MySQL has sufficient privileges - if not, and you cannot configure a temporary database on your personal computer to convert your tables, then ask your shared hosting provider if they will install this feature for you.

Answer 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; 

Answer 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

Answer 5

http://forge.mysql.com/tools/tool.php?id=201

If the column has more than 1 word, this will not work as shown below. The UDF mentioned above may help in this case.

 mysql> select * from names; +--------------+ | name | +--------------+ | john abraham | +--------------+ 1 row in set (0.00 sec) mysql> 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

Answer 6

It works well.

Status UPDATE SET name = CONCAT (UCASE (LEFT (name, 1)), LCASE (SUBSTRING (name, 2)));

Answer 7

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

Answer 8

CREATE 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 FUNCTION

 UPDATE tbl_name SET col_name = UC_FIRST(col_name); 

Answer 9

If anyone tries to use every word written with a space ...

 CREATE FUNCTION response(name VARCHAR(40)) RETURNS VARCHAR(200) DETERMINISTIC BEGIN set @m=''; set @c=0; set @l=1; while @c <= 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 | +--------------+ 

Answer 10

This should work well:

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

Answer 11

 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))); 

Answer 12

  select CONCAT(UCASE(LEFT('CHRIS', 1)),SUBSTRING(lower('CHRIS'),2)); 

The above statement can be used for the first letter of CAPS and rest as lowercase.

Answer 13

Uso is also a simple 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 ;