Capitalize first letter of each word, in existing table

Question

I have an existing table 'people_table', with a field full_name.

Many records have the 'full_name' field populated with incorrect casing. e.g. 'fred Jones' or 'fred jones' or 'Fred jones'.

I can find these errant entries with:

SELECT * FROM people_table WHERE full_name REGEXP BINARY '^[a-z]';

How can I capitalize the first letter of each word found? e.g. 'fred jones' becomes 'Fred Jones'.

Vinicius Braz Pinto · Accepted Answer · 2010-07-19T17:52:36.470

67

There's no MySQL function to do that, you have to write your own. In the following link there's an implementation:

http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/

In order to use it, first you need to create the function in the database. You can do this, for example, using MySQL Query Browser (right-click the database name and select Create new Function).

After creating the function, you can update the values in the table with a query like this:

UPDATE users SET name = CAP_FIRST(name);

edited Jul 19 '10 at 17:52

answered Jul 19 '10 at 03:10

Vinicius Braz Pinto

8,209
3
42
60

1

Thank you for your help. I am not sure how I can use this function to update the records that I already have. (I'm a MySQL noob, I'm afraid.) – SirRatty Jul 19 '10 at 03:40
1

I edited to post, adding information on how to use the function. – Vinicius Braz Pinto Jul 19 '10 at 17:53
Thanks very much Vinicius! I shall try this approach. – SirRatty Jul 20 '10 at 02:33
In my case I had to declare a `DELIMITER` like this `DELIMITER $$ CREATE FUNCTION... BEGIN... END$$ DELIMITER;` – Phemelo Khetho Jul 06 '22 at 18:13

score 50 · Answer 2 · answered Nov 02 '15 at 20:38

If you need to run it just one time, and you don't want to create a function, you can do something really-harcoded as:

UPDATE people_table SET full_name = LOWER(full_name);
UPDATE people_table SET full_name = CONCAT(UPPER(SUBSTR(full_name,1,1)),LOWER(SUBSTR(full_name,2)));
UPDATE people_table SET full_name = REPLACE(full_name,' a',' A');
UPDATE people_table SET full_name = REPLACE(full_name,' b',' B');
UPDATE people_table SET full_name = REPLACE(full_name,' c',' C');
UPDATE people_table SET full_name = REPLACE(full_name,' d',' D');
UPDATE people_table SET full_name = REPLACE(full_name,' e',' E');
UPDATE people_table SET full_name = REPLACE(full_name,' f',' F');
UPDATE people_table SET full_name = REPLACE(full_name,' g',' G');
UPDATE people_table SET full_name = REPLACE(full_name,' h',' H');
UPDATE people_table SET full_name = REPLACE(full_name,' i',' I');
UPDATE people_table SET full_name = REPLACE(full_name,' j',' J');
UPDATE people_table SET full_name = REPLACE(full_name,' k',' K');
UPDATE people_table SET full_name = REPLACE(full_name,' l',' L');
UPDATE people_table SET full_name = REPLACE(full_name,' m',' M');
UPDATE people_table SET full_name = REPLACE(full_name,' n',' N');
UPDATE people_table SET full_name = REPLACE(full_name,' o',' O');
UPDATE people_table SET full_name = REPLACE(full_name,' p',' P');
UPDATE people_table SET full_name = REPLACE(full_name,' q',' Q');
UPDATE people_table SET full_name = REPLACE(full_name,' r',' R');
UPDATE people_table SET full_name = REPLACE(full_name,' s',' S');
UPDATE people_table SET full_name = REPLACE(full_name,' t',' T');
UPDATE people_table SET full_name = REPLACE(full_name,' u',' U');
UPDATE people_table SET full_name = REPLACE(full_name,' v',' V');
UPDATE people_table SET full_name = REPLACE(full_name,' w',' W');
UPDATE people_table SET full_name = REPLACE(full_name,' x',' X');
UPDATE people_table SET full_name = REPLACE(full_name,' y',' Y');
UPDATE people_table SET full_name = REPLACE(full_name,' z',' Z');

This is the best answer. Simple. Do not need create a function — Emerson Rocha, Nov 07 '15 at 20:35
`UPDATE people_table SET full_name = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(UPPER(SUBSTR(full_name,1,1)),LOWER(SUBSTR(full_name,2))),' a',' A'),' b',' B'),' c',' C'),' d',' D'),' e',' E'),' f',' F'),' g',' G'),' h',' H'),' i',' I'),' j',' J'),' k',' K'),' l',' L'),' m',' M'),' n',' N'),' o',' O'),' p',' P'),' q',' Q'),' r',' R'),' s',' S'),' t',' T'),' u',' U'),' v',' V'),' w',' W'),' x',' X'),' y',' Y'),' z',' Z')` — Benj F, Apr 16 '16 at 00:00
Terrible code. What happens if I use Unicode with characters like Cyrillic, Chinese etc.??? — Nikolay Ivanov, Nov 07 '17 at 10:33
Then this quick-solution don't fix your problem and you have to use a function, but that don't make this a "terrible code". If your real problem it's about just cyrilic or latin you can add those upper characters because there no so much, and for chinese capitals are exchangable on regular words. — glerendegui, Nov 08 '17 at 01:49
I agree this is the simplest solution for a 1-time use if you do not want to create functions. However, it does not consider hyphenated names. E.g., "peter smith-thompson" would become "Peter Smith-thompson". So you need to run another round of updates with ```UPDATE people_table SET full_name = REPLACE(full_name,'-a','-A');``` etc. — Pete, Dec 22 '22 at 10:15

Sergio Abreu · Answer 3 · 2017-01-27T20:08:47.027

If you want to capitalize all words, it will be needed to invoke a custom function.

-- may help:
-- DROP function if exists capitalize;

DELIMITER $$
CREATE FUNCTION `capitalize`(s varchar(255)) RETURNS varchar(255) 
BEGIN
  declare c int;
  declare x varchar(255);
  declare y varchar(255);
  declare z varchar(255);

  set x = UPPER( SUBSTRING( s, 1, 1));
  set y = SUBSTR( s, 2);
  set c = instr( y, ' ');

  while c > 0
    do
      set z = SUBSTR( y, 1, c);
      set x = CONCAT( x, z);
      set z = UPPER( SUBSTR( y, c+1, 1));
      set x = CONCAT( x, z);
      set y = SUBSTR( y, c+2);
      set c = INSTR( y, ' ');     
  end while;
  set x = CONCAT(x, y);
  return x;
END$$

DELIMITER ;

Now you do this way:

 UPDATE mytable SET thefield = capitalize(thefield);

This will only fix the first word, not the entire string. Eg "fred jones" => "Fred jones". Should be going for "Fred Jones". — jamesthollowell, Aug 31 '15 at 13:37

Eric Leroy · Answer 4 · 2015-11-28T20:45:29.773

Here are two useful functions by Nicholas Thompson. You can set the 3rd variable of UC_DELEMITER to false, and the second to " " for the capitalization of more than one word.

UC_FIRST Capitalize any given string - This function is a clone of the ucfirst function in PHP.

DROP FUNCTION IF EXISTS UC_FIRST;
CREATE FUNCTION UC_FIRST(oldWord VARCHAR(255)) RETURNS VARCHAR(255)
RETURN CONCAT(UCASE(SUBSTRING(oldWord, 1, 1)),SUBSTRING(oldWord, 2));

UC_DELIMITER Capitalize with a delimiter in between words

DROP FUNCTION IF EXISTS UC_DELIMITER;
DELIMITER //
CREATE FUNCTION UC_DELIMITER(
   oldName VARCHAR(255), delim VARCHAR(1), trimSpaces BOOL
) 
  RETURNS VARCHAR(255)
BEGIN
SET @oldString := oldName;
SET @newString := "";

tokenLoop: LOOP
    IF trimSpaces THEN SET @oldString := TRIM(BOTH " " FROM @oldString);          END IF;

SET @splitPoint := LOCATE(delim, @oldString);

IF @splitPoint = 0 THEN
  SET @newString := CONCAT(@newString, UC_FIRST(@oldString));
  LEAVE tokenLoop;
END IF;

   SET @newString := CONCAT(@newString, UC_FIRST(SUBSTRING(@oldString, 1, @splitPoint)));
   SET @oldString := SUBSTRING(@oldString, @splitPoint+1);
END LOOP tokenLoop;

RETURN @newString;
END//
DELIMITER ;

Examples:

SELECT UC_DELIMITER('eric-leroy','-',TRUE);
Eric-Leroy

Function's Webpage

score 4 · Answer 5 · edited Feb 14 '15 at 12:57

I tried the code from above but had syntax errors on function, so could not create it. Wrote this for latest version of MySQL if it helps anyone

CREATE FUNCTION  `CAP_FIRST`(input VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE len INT;
    DECLARE i INT;
    DECLARE charnum INT;
    declare SortedName varchar(255);

    SET len   = CHAR_LENGTH(input);
    SET input = LOWER(input);
    SET i = 1;
    set charnum = 1;
    set SortedName = '';


    WHILE (i <= len) DO
        if charnum = 1 then
            set SortedName = concat(SortedName,upper(mid(input,i,1)));
            set charnum = charnum + 1;
        else
            if mid(input,i,1) = ' ' then
                set SortedName = concat(SortedName,' ');
                set charnum = 1;
            else
                set SortedName = concat(SortedName,mid(input,i,1));
                set charnum = charnum + 1;
            end if;

        end if;


        SET i = i + 1;
    END WHILE;

    RETURN SortedName;
END

I still get: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4" — Jester, Apr 15 '18 at 23:22

score 4 · Answer 6 · answered Jun 21 '17 at 12:28

DELIMITER $$
CREATE FUNCTION `capitalize`(s varchar(255)) RETURNS varchar(255) 
BEGIN
  declare c int;
  declare x varchar(255);
  declare y varchar(255);
  declare z varchar(255);

  set x = UPPER( SUBSTRING( s, 1, 1));
  set y = lower(SUBSTR( s, 2));
  set c = instr( y, ' ');

  while c > 0
    do
      set z = SUBSTR( y, 1, c);
      set x = CONCAT( x, z);
      set z = UPPER( SUBSTR( y, c+1, 1));
      set x = CONCAT( x, z);
      set y = SUBSTR( y, c+2);
      set c = INSTR( y, ' ');     
  end while;
  set x = CONCAT(x, y);
  return x;
END$$

DELIMITER ;

Create above function to set First character to capital of each words

score 3 · Answer 7 · edited Feb 08 '19 at 16:52

3

Simply :

SELECT
CONCAT(UCASE(LEFT(firstname, 1)), LCASE(SUBSTRING(firstname, 2))) as firstname
FROM PEOPLE

edited Feb 08 '19 at 16:52

Zoe

27,060
21
118
148

answered Feb 08 '19 at 16:49

Thomas

31
1

2

This will only capitalize the first name (not each word found as asked) – mathiasfk Feb 08 '19 at 18:02

score 0 · Answer 8 · answered Jul 30 '19 at 13:51

No need for creating a function if it is a one timer. The below works just fine:

-- Capitalize first letter of each word in r.name field
SELECT TRIM(CONCAT(
   CONCAT(UPPER(SUBSTRING(cname1,1,1)),SUBSTRING(cname1,2)) , " ",
   CONCAT(UPPER(SUBSTRING(cname2,1,1)),SUBSTRING(cname2,2)) , " ",
   CONCAT(UPPER(SUBSTRING(cname3,1,1)),SUBSTRING(cname3,2))))
FROM (
   SELECT
     @num_spaces := 1 + LENGTH(c_name) - LENGTH(REPLACE(c_name, ' ', '')) AS 
     num_spaces,
     SUBSTRING_INDEX(CONVERT(c_name,CHAR), ' ', 1) AS cname1,
     IF(@num_spaces > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(c_name, ' ', 2), ' ', -1), '') AS cname2,
     IF(@num_spaces > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(c_name, ' ', 3), ' ', -1), '') AS cname3
     FROM (SELECT (CASE 
        WHEN UPPER(r.name)COLLATE latin1_general_cs =r.name THEN LOWER(TRIM(r.name))
        ELSE TRIM(r.name)
        END) AS c_name,r.name
        FROM table r) cr) ncr;

NOTE: The IF clause should be equal to or more than the value of @num_spaces. The current sql will take care of at max 3 words. You may add more if required.

score 0 · Answer 9 · answered Jul 14 '20 at 11:16

0

firstly create a function

DROP FUNCTION IF EXISTS UC_FIRST;
CREATE FUNCTION UC_FIRST(oldWord VARCHAR(255)) RETURNS VARCHAR(255) 
RETURN CONCAT(UCASE(SUBSTRING(oldWord, 1, 1)),SUBSTRING(oldWord, 2));

and then use this query

UPDATE mytable SET thefield = UC_FIRST(thefield);

answered Jul 14 '20 at 11:16

Anjani Barnwal

1,362
1
17
23

This only capitalizes the first letter of the first word, not of all words. – OMA Apr 21 '22 at 00:33

score 0 · Answer 10 · answered May 05 '21 at 10:51

Answer from Eric Leroy is the most interesting answer here, but it misses two things:

Lower case in UC_FIRST function: if you want to uppercase the first letter for a word, it means that you also want the other letters to be lowercase. It works with a full lowercase entry, but if ever in entry you get mixed cases such as «wOrD», you would get «WOrD» when you want «Word».
Depending on the mysql version and configuration, you may need the deterministic key word in the function definition.

Here is the updated code to create the functions:

DROP FUNCTION IF EXISTS UC_FIRST;
CREATE FUNCTION UC_FIRST(oldWord VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC
RETURN CONCAT(UCASE(SUBSTRING(oldWord, 1, 1)),LCASE(SUBSTRING(oldWord, 2)));

DROP FUNCTION IF EXISTS UC_DELIMITER;
DELIMITER //
CREATE FUNCTION UC_DELIMITER(
   oldName VARCHAR(255), delim VARCHAR(1), trimSpaces BOOL
) 
  RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
SET @oldString := oldName;
SET @newString := "";

tokenLoop: LOOP
    IF trimSpaces THEN SET @oldString := TRIM(BOTH " " FROM @oldString);          END IF;

SET @splitPoint := LOCATE(delim, @oldString);

IF @splitPoint = 0 THEN
  SET @newString := CONCAT(@newString, UC_FIRST(@oldString));
  LEAVE tokenLoop;
END IF;

   SET @newString := CONCAT(@newString, UC_FIRST(SUBSTRING(@oldString, 1, @splitPoint)));
   SET @oldString := SUBSTRING(@oldString, @splitPoint+1);
END LOOP tokenLoop;

RETURN @newString;
END//
DELIMITER ;

With the following request:

SELECT UC_DELIMITER('ThIs IS an ExAMplE oF MIxeD CaseS sTRing',' ',TRUE);

You will get:

This Is An Example Of Mixed Cases String

score 0 · Answer 11 · answered Jul 30 '22 at 10:24

This can be done by using LOWER, UPPER and SUBSTRING functions, see below sample query

Select CONCAT(UPPER(SUBSTRING(name,1,1)),LOWER(SUBSTRING(name,2))) AS Name from table;

In an UPDATE query this will be

UPDATE table1 SET name = CONCAT(UPPER(SUBSTRING(name,1,1)),LOWER(SUBSTRING(name,2))) WHERE 1

score -2 · Answer 12 · answered Sep 04 '16 at 11:50

-2

on phpmyadmin, run this UPDATE table_name SET Column_Name = LOWER(Column_Name) then in the html page that displays the data from the database table use css text-transform: capitalize;

answered Sep 04 '16 at 11:50

Njoroge Mathu

45
6

score -2 · Answer 13 · answered Feb 16 '17 at 18:11

The Proper function in Excel (or google sheets does exactly what you want.

So, export your mysql table as CSV and into Excel (or google sheets). Then use the = Proper(*text_to_capitalize*) to capitalize the first letter of each word.

Then just export that excel sheet as CSV back into your database.

score -2 · Answer 14 · edited Apr 04 '17 at 04:52

-2

If you are using PHP then...

try{
  $con = new PDO("mysql:host=localhost;dbname=dbasename", "root", "");
}
catch(PDOException $e){
  echo "error" . $e-getMessage();
}

$select = $con->prepare("SELECT * FROM table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();

while($data=$select->fetch()) {

  $id = $data['id'];
  $column = $data['column'];
  $column = ucwords(strtolower($column)); // Capitalize each word

  $update = $con->prepare("UPDATE table SET column=:column WHERE id='$id'");
  $update->bindParam(':column', $column);
  $update->execute();
}

edited Apr 04 '17 at 04:52

Peter Mortensen

30,738
21
105
131

answered Apr 04 '17 at 01:38

This question is specifically about MySQL methods. The value of doing it in MySQL is it can be done regardless of programming language. It can also be used for data views and derivative columns and such. PHP can indeed handle that, but a PHP solution here is not a solution. – Giacomo1968 Sep 21 '21 at 04:10

Capitalize first letter of each word, in existing table

14 Answers14

Linked

Related