Mysql Update Records

Question

I have to update one column from my user table . Current record in User Table

**id , user_name**
1    , sachin rathore
2    , dilip CHOUHAN
3    , GariMA JAIN

I want to update user_name column like this

1 , Sachin Rathore
2 , Dilip Chouhan
3 , Garima Jain

User column should be in titlize form means first letter of each word should be capital and remain small letter

Answer 1

THere's no build in function that can transform text in this way. You'd need to write a script that will do it. For example PHP has ucwords() function that you could use.

http://www.php.net/manual/en/function.ucwords.php

Answer 2

Here's a query that will do it

UPDATE SET table SET user_name= CAP_FIRST(CONCAT(SUBSTRING_INDEX(user_name, ' ',-1), ' ', SUBSTRING_INDEX(user_name, ' ',1)))

It relies on a custom built function to capitalize each first letter, namely CAP_FIRST, as found here: http://joezack.com/index.php/2008/10/20/mysql-capitalize-function/

Here's another one: http://forums.mysql.com/read.php?20,265978,266040#msg-266040