I wrote a little MySQL function to capitalize the first letter of every word in a string. I thought I’d share since I wasn’t able to google for it.
CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE len INT;
DECLARE i INT;
SET len = CHAR_LENGTH(input);
SET input = LOWER(input);
SET i = 0;
WHILE (i < len) DO
IF (MID(input,i,1) = ' ' OR i = 0) THEN
IF (i < len) THEN
SET input = CONCAT(
LEFT(input,i),
UPPER(MID(input,i + 1,1)),
RIGHT(input,len - i - 1)
);
END IF;
END IF;
SET i = i + 1;
END WHILE;
RETURN input;
END;
So running the following code...
SELECT CAP_FIRST(
'this is totally like @ TEST 1 right!'
)
Returns the string "This Is Totally Like @ Test 1 Right!"
I would rather have regex'd it, but I couldn't find any sort of regex replace function in the docs.
Pingback: Capitalize the first letter of each word using MYSQL - Microsoft Windows Vista Community Forums - Vistaheads
AOA
Thanks Friend for this.
Here is my modified version.
DELIMITER $$
DROP FUNCTION IF EXISTS `sharex`.`Capitalize`$$
CREATE DEFINER=`root`@`%` FUNCTION `Capitalize`(InputString VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
DECLARE Input_Length INT;
DECLARE Count INT;
SET Input_Length = CHAR_LENGTH(InputString);
SET InputString = LOWER(InputString);
SET Count = 0;
WHILE (Count < Input_Length) DO
IF (MID(InputString,Count,1) = ‘ ‘ OR MID(InputString,Count,1) = ‘.’ OR Count = 0) THEN
SET InputString = CONCAT(LEFT(InputString,Count),UPPER(MID(InputString,Count + 1,1)),
RIGHT(InputString,Input_Length – Count – 1));
ELSE
SET InputString = CONCAT(LEFT(InputString,Count),LOWER(MID(InputString,Count + 1,1)),
RIGHT(InputString,Input_Length – Count – 1));
END IF;
SET Count = Count + 1;
END WHILE;
RETURN InputString;
END$$
DELIMITER ;
Pingback: Capitalize the first letter of each word using MYSQL | keyongtech
Awesome function JOEZACK! Works like a charm. Thanks for sharing!
JOEZACK thanks mate, needed this exact thing and works perfect.
thanks again
DUDE! This layout is awesome. I’m diggin’ the chrome style. Oh, and on a side note, you’re a sick sick individual for writing SQL like that :p lol. Thanks, this is going to help out
Hi JOEZACK,
It’s probably me but when I get down to;
DECLARE len INT;
It bombs with a mysql error…
Have I missed something?
You are the man, I was testing another functions supposed to do this, and they was failing with non alfabetic characters
Thanks a lot for this. Really useful…
I hope you don’t mind if I publish this on my blog too? I will link back to you, of course…
No problem, glad I could help!
I’m with Jools.. bombs on DECLARE len INT;
– Nice effort though.
Great script. Worked like a charm!
For you guys that are getting the mysql error…
The default delimiter in mysql is shown at the bottom of the sql query window as a semi-colon. You have to change that. Make it a tilde and the script will work perfectly.
Hallo, Neat Post. Es gibt ein Problem mit Ihrer Website in Internet Explorer, würde dies zu testen … also noch ist der Marktführer und ein großer Teil der Menschen wird Ihre wunderbare schriftlich wegen dieses Problems vermissen.
Pingback: Fungsi MySQL Huruf Kapital Pada Awal Kata » Mangifero Scratchlog
Pingback: Anonymous
This works perfectly, thanks!
It it possible to apply this to an entire column inside a table? In a table, I have a column with a string inside it, and I need to capitalize the first letter of every word.
Is this possible using your function?
Thanks,
Hunter
Slightly modified version of your code, to capitalize first char only for words longer than X character.
Una versione leggermente modificata per considerare solo le parole più lunghe di X caratteri.
DELIMITER $$
DROP FUNCTION IF EXISTS Capitalize$$
CREATE FUNCTION Capitalize (InputString VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
DECLARE Input_Length INT;
DECLARE Count INT;
DECLARE Last_Word INT;
DECLARE Min_Char INT;
SET Input_Length = CHAR_LENGTH(InputString);
SET InputString = LOWER(InputString);
SET Count = 0;
SET Last_Word = 0;
SET Min_Char = 3;
WHILE (Count Min_Char) THEN
SET InputString = CONCAT( LEFT(InputString,Last_Word),
UPPER(MID(InputString,Last_Word + 1,1)),
RIGHT(InputString,Input_Length – Last_Word – 1));
END IF;
SET Last_Word = Count;
END IF;
SET Count = Count + 1;
END WHILE;
RETURN InputString;
END$$
DELIMITER ;
thanks for the script
is it possible to modify the script to accept an additional parameter for capitalizing words that are longer than X characters? The script by Stefano doesn’t work
Thanks
Alex
No problem, @Alex D
All you have to do is modify the arguments to take in a length:
CREATE FUNCTION CAP_FIRST (input VARCHAR(255), maxLength int)
and then add an if:
IF LENGTH(InputString) > maxLength THEN
SET InputString = CONCAT( LEFT(InputString,Last_Word),
END IF
@me Can please post the full working script that incorporates maxLength? Your post seems to imply tweaking the initial script but then the If block seems to imply using the other, non-working one. Thanks!
– Capitalizes the first letter of every word where word has more than 2 chars and is not “the” or “and”.
DELIMITER //
DROP FUNCTION IF EXISTS CAP_FIRST;
CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE len INT;
DECLARE i INT;
DECLARE j INT;
DECLARE wordLength INT;
DECLARE word VARCHAR(255);
DECLARE letter CHAR(1);
DECLARE str VARCHAR(255);
SET len = CHAR_LENGTH(input);
SET i = 0;
SET str = ”;
WHILE (i < len) DO
IF (MID(input,i,1) = ' ' OR i = 0) THEN
IF (i < len) THEN
— look ahead for word length
SET j = 1;
SET word = '';
wordLoop: WHILE (i+j-1 2 AND word != ‘and’ AND word != ‘the’) THEN
SET input = CONCAT(
LEFT(input,i),
UPPER(MID(input,i + 1,1)),
RIGHT(input,len – i – 1)
);
END IF;
END IF;
END IF;
SET i = i + 1;
END WHILE;
– RETURN str;
RETURN input;
END //
DELIMITER ;
This is my version with exempt words. This version has mysql return each word in the string to check against. It can be easily adapted to word length restriction if needed.
DELIMITER $$
DROP FUNCTION IF EXISTS `CAP_FIRST`$$
CREATE FUNCTION `CAP_FIRST`(input VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE endstring INT;
DECLARE i INT;
DECLARE word VARCHAR(255);
DECLARE output VARCHAR(255);
SET input = CONCAT(LOWER(input),’ ||’);
SET i = 1;
SET endstring = 0;
SET word = ”;
SET output = ”;
WHILE (endstring = 0) DO
SET word = SUBSTRING_INDEX(SUBSTRING_INDEX(input, ‘ ‘,i),’ ‘,-1);
IF (word != ‘||’) THEN
IF (word NOT IN (‘and’,'with’,'for’,'a’)) THEN
SET word = CONCAT(UPPER(LEFT(word,1)),MID(word,2));
END IF;
SET output = CONCAT(output,’ ‘,word);
ELSE
SET endstring = 1;
END IF;
SET i = i + 1;
END WHILE;
RETURN output;
END$$
DELIMITER ;
Pingback: How to capitalize first letters of words in MySQL | Local Wisdom, Inc.
Hey Changed the Delimiter and it worked like a Breeze
posting your original Code with THe DELIMITER
DELIMITER //
CREATE FUNCTION CAP_FIRST (input VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE len INT
;
DECLARE i INT
;
SET len = CHAR_LENGTH(input);
SET input = LOWER(input);
SET i = 0;
WHILE (i < len) DO
IF (MID(input,i,1) = ' ' OR i = 0) THEN
IF (i < len) THEN
SET input = CONCAT(
LEFT(input,i),
UPPER(MID(input,i + 1,1)),
RIGHT(input,len – i – 1)
);
END IF;
END IF;
SET i = i + 1;
END WHILE;
RETURN input;
END;
//
Thanks so much Joezack! It has save me lots of work!