Update a field in SQL

Question

I have several tables that have a common field (column) called LastName in a MySQL database. Several of the rows in these tables are in mixed case so they don't get selected properly when doing a SELECT.

How can I convert those columns to all UPPER CASE? I can easily handle any new entries to convert them to upper case, but the existing records I'm not so sure about.

You've got the answers below, but you can also add WHERE LastName <> UPPER(LastName) to keep transaction size down. — jarlh
– jarlh, Commented Jul 10, 2015 at 9:37
@jarlh : I was just going to add the same comment - but with the caveat that this may have an impact on query speed with large tables. — PaulF
– PaulF, Commented Jul 10, 2015 at 9:39
Does MySQL support collations? I'd rather alter the column to a case insensitive collation in that case, which mean names is stored as enterd, but search is done case insensitive. — jarlh
– jarlh, Commented Jul 10, 2015 at 9:41
@jarlh : I have just checked that - unless the string is a binary string - then string comparisons should be case-insensitive. dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html — PaulF
– PaulF, Commented Jul 10, 2015 at 9:43
Speed is not an issue because this is going to be a one time conversion.\ — user2021539
– user2021539, Commented Jul 10, 2015 at 9:55

PaulF · Accepted Answer · 2015-07-10 09:34:56Z

would do the job

update table set LastName=UPPER(LastName);

NOTE - if you are running from MySQL workbench you may have to disable safety mode or add a where clause (eg WHERE id>0) otherwise it wont run.

Evans Murithi · Accepted Answer · 2015-07-10 09:36:26Z

1

this would work:

UPDATE table_name SET `column_name` = UPPER( `column_name` )

answered Jul 10, 2015 at 9:36

Evans Murithi

3,2771 gold badge24 silver badges26 bronze badges

Comments

Sachu · Accepted Answer · 2015-07-10 09:36:00Z

You can use the string function UPPER() to make the column value to upper

update Your_table set LastName=UPPER(LastName)

Collectives™ on Stack Overflow

Update a field in SQL

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related