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))
This question is related to
mysql
string
capitalize
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 ;
This should work nicely:
UPDATE tb_Company SET CompanyIndustry =
CONCAT(UPPER(LEFT(CompanyIndustry, 1)), SUBSTRING(CompanyIndustry, 2))
UPDATE tb_Company SET CompanyIndustry = UCASE(LEFT(CompanyIndustry, 1)) +
SUBSTRING(CompanyIndustry, 2, LEN(CompanyIndustry))
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);
You can use a combination of UCASE(), MID() and CONCAT():
SELECT CONCAT(UCASE(MID(name,1,1)),MID(name,2)) AS name FROM names;
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> 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...
select CONCAT(UCASE(LEFT('CHRIS', 1)),SUBSTRING(lower('CHRIS'),2));
Above statement can be used for first letter CAPS and rest as lower case.
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 <= 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 |
+--------------+
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
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.
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)));
This is working nicely.
UPDATE state SET name = CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2)));
Source: Stackoverflow.com