Regular database - MySql

Question

MySql Datas;

   ID       Fname         Lname     
+------+-------------+-----------+
+  1   +    emre     +  surname  +
+  2   +    naMe2    +  suRnaMe2 +
+  3   +    namE3    +  SurnamE3 +
+ 1980 +    nAmE3    +  suRNamE3 +
+------+-------------+-----------+

I want to:

   ID       Fname         Lname     
+------+-------------+-----------+
+  1   +    Emre     +  Surname  +
+  2   +    Name2    +  Surname2 +
+  3   +    Name3    +  Surname3 +
+ 1980 +    Name4    +  Surname4 +
+------+-------------+-----------+

How can I make the editing process? - Is it possible with SQL Query?

Thank you!

1. http://whathaveyoutried.com, 2. learn about `UPDATE` queries. — Tomasz Kowalczyk, Oct 05 '12 at 08:21
http://stackoverflow.com/questions/4263272/capitalize-first-letter-mysql — MrCode, Oct 05 '12 at 08:23
This topic is already discussed here. [Capitalize first letter][1] [1]: http://stackoverflow.com/questions/3278207/mysql-capitalize-first-letter-of-each-word-in-existing-table — Palanikumar, Oct 05 '12 at 08:30
Even though unicode characters will behave as expected note that uppercase of `i` will become `I` (dotless i) and lowercase of `I` will become `i` if you go about using standard SQL case functions. If you have Turkish names there you might want to use some conditions in your query. — inhan, Oct 05 '12 at 08:38

John Woo · Accepted Answer · 2012-10-05T08:37:21.753

3

Try simulating it,

SELECT CONCAT(UPPER(SUBSTRING(Fname, 1, 1)), LOWER(SUBSTRING(Fname FROM 2))) AS properFirstName,
       CONCAT(UPPER(SUBSTRING(Lname, 1, 1)), LOWER(SUBSTRING(Lname FROM 2))) AS properLastName
FROM table1

SQLFiddle Demo

UPDATE tableName
SET Fname = CONCAT(UPPER(SUBSTRING(Fname, 1, 1)), LOWER(SUBSTRING(Fname FROM 2))),
    LName = CONCAT(UPPER(SUBSTRING(Fname, 1, 1)), LOWER(SUBSTRING(Fname FROM 2)))

edited Oct 05 '12 at 08:37

answered Oct 05 '12 at 08:22

John Woo

258,903
69
498
492

The query succeeded, but the list does not change? Thank you for interest – Saracoglu Oct 05 '12 at 08:35
yep because it is `SELECT` you didn't mention that you want to `UPDATE` the table, Answer has been updated. – John Woo Oct 05 '12 at 08:36

score 1 · Answer 2 · answered Oct 05 '12 at 08:26

You can use something like that:

UPDATE table
SET Fname = CONCAT(UCASE(LEFT(Fname, 1)), LCASE(SUBSTRING(Fname, 2))),
    Fname = CONCAT(UCASE(LEFT(Lname, 1)), LCASE(SUBSTRING(Lname, 2)));

This should be update all your value:

wOrld will become World
Help will remain Help
sEE YOU later1 will become See you later1

I suggest you to try it on a backup table before to use it on the main one.

score 0 · Answer 3 · answered Oct 05 '12 at 08:40

Check This is working .

First letter of a string to a capital

UPDATE table SET field = CONCAT(UPPER(LEFT(field, 1)), SUBSTRING(field, 2))

and if you want to have all other characters lowercased:

UPDATE table SET field = CONCAT(UPPER(LEFT(field, 1)), LOWER(SUBSTRING(field, 2)))

Regular database - MySql

3 Answers3

SQLFiddle Demo