Is there a MySQL command to convert a string to lowercase?

Question

I have a MySQL database of keywords that are presently mixed-case. However, I want to convert them all to lowercase. Is there an easy command to do this, either using MySQL or MySQL and PHP?

I guess you're also using a dump from a friend who thought it would be a good idea to store usernames with capitalized letters. — Buffalo, Jul 04 '13 at 15:46

score 259 · Accepted Answer · answered Oct 21 '08 at 13:03

259

UPDATE table SET colname=LOWER(colname);

answered Oct 21 '08 at 13:03

Paul Dixon

287,944
49
307
343

if the collation of a column is set to `ascii_bin`, wont this automatically convert everything to lowercase or am i mistaken? – oldboy Jul 29 '19 at 01:01

score 29 · Answer 2 · answered Oct 21 '08 at 13:03

29

Yes, the function is LOWER() or LCASE() (they both do the same thing).

For example:

select LOWER(keyword) from my_table

answered Oct 21 '08 at 13:03

Jon Grant

11,155
2
34
56

score 12 · Answer 3 · answered Oct 21 '08 at 13:02

12

SELECT LOWER(foo) AS foo FROM bar

answered Oct 21 '08 at 13:02

Greg

307,243
53
363
328

score 9 · Answer 4 · answered Oct 21 '08 at 13:07

You can use the functions LOWER() or LCASE().

These can be used both on columns or string literals. e.g.

SELECT LOWER(column_name) FROM table a;

or

SELECT column_name FROM table a where column = LOWER('STRING')

LCASE() can be substituted for LOWER() in both examples.

score 5 · Answer 5 · answered Oct 21 '08 at 13:03

5

Did you try looking it up? Google, manual...

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_lower

mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'

answered Oct 21 '08 at 13:03

myplacedk

1,534
2
14
19

score 3 · Answer 6 · edited Jan 09 '18 at 15:13

3

Simply use:

UPDATE `tablename` SET `colnameone`=LOWER(`colnameone`);

or

UPDATE `tablename` SET `colnameone`=LCASE(`colnameone`);

Both functions will work the same.

edited Jan 09 '18 at 15:13

dferenc

7,637
12
39
46

answered Jan 09 '18 at 11:55

Vi8L

848
9
11

score 0 · Answer 7 · answered Sep 19 '18 at 15:10

0

Interesting to note that the field name is renamed and if you reference it in a function, you will not get its value unless you give him an alias (that can be its own name)

Example: I use a function to dynamically get a field name value:

function ColBuilder ($field_name) {
…
While ($result = DB_fetch_array($PricesResult)) {
$result[$field_name]
}
…
}

my query being: SELECT LOWER(itemID), … etc..

needed to be changed to: SELECT LOWER(itemID) as itemID, … etc..

answered Sep 19 '18 at 15:10

HD FrenchFeast

1

This does not sound like an answer, or? – Dieter Meemken Sep 19 '18 at 15:33
it completes the answer, warning that using LOWER() in specific situations may not provide the expected result without additional aliasing. – HD FrenchFeast Sep 20 '18 at 16:39

score -3 · Answer 8 · answered Mar 24 '14 at 13:50

-3

use LOWER function to convert data or string in lower case.

select LOWER(username) from users;

or

select * from users where LOWER(username) = 'vrishbh';

answered Mar 24 '14 at 13:50

uma

2,814
24
20

6

What does this add that wasn't in the *five year old* answers? – Andrew Mar 24 '14 at 14:31

score -6 · Answer 9 · answered Oct 21 '08 at 13:06

-6

I believe in php you can use

strtolower()

so you could make a php to read all the entries in the table then use that command to print them back as lower case

answered Oct 21 '08 at 13:06

Rodent43

161
1
3

The OP requested MySQL or MySQL/PHP. – starryknight64 Mar 28 '16 at 19:28

Is there a MySQL command to convert a string to lowercase?

9 Answers9