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'.

Answer 1

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);

Answer 2

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');

Answer 3

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);