I have a row in database, If i write this query
select * from table1;
The following query will give the desired result
james anderson
stuart broad
Can i have the query where the first letter of the word should be in Uppercase
i.e the output must be
James Anderson
Stuart Broad
Have tried with following query
UPDATE people_table SET full_name = CONCAT(UPPER(SUBSTR(full_name,1,1)),LOWER(SUBSTR(full_name,2)));
This query yields only the first Word of the row in uppercase.
I've come across this kind of thing before, it's referred to as an INITCAP function.
It's not a built in function but check this link out for some mysql examples.
Have pasted the solution from this below:
DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`initcap`$$
CREATE FUNCTION `initcap`(x char(30)) RETURNS char(30) CHARSET utf8
BEGIN
SET @str='';
SET @l_str='';
WHILE x REGEXP ' ' DO
SELECT SUBSTRING_INDEX(x, ' ', 1) INTO @l_str;
SELECT SUBSTRING(x, LOCATE(' ', x)+1) INTO x;
SELECT CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(@l_str,1,1)),LOWER(SUBSTRING(@l_str,2)))) INTO @str;
END WHILE;
RETURN LTRIM(CONCAT(@str, ' ', CONCAT(UPPER(SUBSTRING(x,1,1)),LOWER(SUBSTRING(x,2)))));
END$$
DELIMITER ;
Usage:
select initcap('This is test string');
I'm assuming you're familiar with user defined functions, but to use this in an update it'd be kinda like:
update myTable set name = initcap(name) where ...
Hope this is helpful!
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
