MySQL: Replace a specific MySQL Value with an SQL Query

Question

I have a database with a column 'Date'.

At first, the person whom setup the database, he wrongly set the column Type to varchar(9).

The date's are 01-01-2012 (10 Characters), thus on every single date is missing the last number.

enter image description here

Is it possible now to replace all 201 with 2012 using a MySQL Query?

Thanks

John Woo · Accepted Answer · 2012-11-13 14:21:34Z

4

UPDATE tableName
SET columnName = REPLACE(columnName, '201', '2012')

OR

UPDATE tableName
SET columnName = CONCAT(columnName, '2')

answered Nov 13, 2012 at 14:07

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CONCAT adds a value at the end?

Ray · Accepted Answer · 2012-11-13 14:08:23Z

0

First increase the table size to varchar(10). Then

UPDATE tableName
SET columnName = REPLACE(columnName, '201', '2012')

answered Nov 13, 2012 at 14:08

Ray

41.6k22 gold badges110 silver badges144 bronze badges

Im using phpMyAdmin. I think I need to use `` ? UPDATE Fuelconsumption SET date = REPLACE(date, '201', '2012')

you will only use backtick if your column name is a reserved keyword or has space on it, hello world

@jQuerybeast date isn't a reserved word in mysql, so no escaping needed