Capitalize first letter in all rows MySQL

Question

I need a script to run that will update 'given_name' column and change all first letters to capital..

I currently have this code.. and nothing happens..

DROP PROCEDURE IF EXISTS CAPITALIZE;
DELIMITER $$

CREATE PROCEDURE CAPITALIZE()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
SELECT COUNT(*) INTO n FROM users;
-- SET n = (SELECT COUNT(*) FROM users);
SET i=0;
WHILE (i<=n) DO 
  UPDATE users SET given_name = CONCAT( UPPER(SUBSTR(given_name,1,1)), SUBSTR(given_name,2)) WHERE user_id = n;
  UPDATE users SET last_name = CONCAT( UPPER(SUBSTR(last_name,1,1)), SUBSTR(last_name,2)) WHERE user_id = n;

  SET i = (i + 1);
END WHILE;
END $$
DELIMITER ;

I need to run the script in MySQL that will do the same function .. — EdSniper, Oct 26 '15 at 03:25

Tim Biegeleisen · Accepted Answer · 2015-10-26T04:01:50.697

4

As @Drew mentioned, you might be better off just doing a blanket UPDATE statement for the entire users table:

UPDATE users
SET given_name = CONCAT( UPPER(SUBSTR(given_name,1,1)), LOWER(SUBSTR(given_name,2))),
    last_name = CONCAT( UPPER(SUBSTR(last_name,1,1)), LOWER(SUBSTR(last_name,2)))

To turn off your safe update mode, which is preventing the above query from working, type this:

SET SQL_SAFE_UPDATES = 0;

Here is a working fiddle which demonstrates the above query:

SQLFiddle

edited Oct 26 '15 at 04:01

answered Oct 26 '15 at 03:25

Tim Biegeleisen

502,043
27
286
360

I would suggest no while loop. Just do it directly in mysql. A single update stmt with set given, last – Drew Oct 26 '15 at 03:27
1

+Tim Biegeleisen - you got it right but when i run this script it still doesn't affect any rows or doesn't make any change – EdSniper Oct 26 '15 at 03:29
Please make your life easier and just use `UPDATE` as a query from your console. Much easier, with a pleasant aftertaste. – Tim Biegeleisen Oct 26 '15 at 03:30
Missed the other part of his while loop, sorry. – Tim Biegeleisen Oct 26 '15 at 03:31
2

My senior told me to use stored proc, do you think my script above will work. but then thanks for the simpler version. I'm going to use that if I can change the settings on the main server. – EdSniper Oct 26 '15 at 03:35
1

@Drew can you help me to lower case the succeeding letters. Example. data is : aSDvv.. must be converted to Asdvv – EdSniper Oct 26 '15 at 03:45
@EdwardBeringuela Just wrap the second `SUBSTR()` call with `LOWER()`. – Tim Biegeleisen Oct 26 '15 at 03:49
1

What if the given name was TIM DREW what would it do. – EdSniper Oct 26 '15 at 03:54
1

I need to do it like Tim Drew sorry for my bad english – EdSniper Oct 26 '15 at 03:55
The query in my answer should handle the case `TIM DREW` and _any_ string, to get the format you want. Try it out. – Tim Biegeleisen Oct 26 '15 at 03:56
1

What it did was Tim drew, not Tim Drew. – EdSniper Oct 26 '15 at 03:57
1

@TimBiegeleisen where is answer for Tim Drew xD – EdSniper Oct 26 '15 at 04:26
You should really open a new question. This is substantially different than your original question. – Tim Biegeleisen Oct 26 '15 at 05:03
1

http://stackoverflow.com/a/3278236/1816093 Now mark this Answer as correct with the happy Green check mark – Drew Oct 26 '15 at 05:06
1

It doesn't handle UTF-8 characters can you help me with it @Drew – EdSniper Oct 26 '15 at 05:57

Capitalize first letter in all rows MySQL

1 Answers1