Update field with new string made from characters in existing value

Question

field is meta_value and current value is like 01/01/2006

i want to change it to a string 20060101 so thought i could get the value and update it with the characters in the correct order.

ie

UPDATE wp_postmeta SET meta_value = meta_value[9]meta_value[8]meta_value[7]meta_value[6] 

Answer 1

Assuming that meta_value is always dd/mm/yyyy and you are looking for yyyymmdd (which isn't clear), try this:

UPDATE wp_postmeta 
SET meta_value = CONCAT(SUBSTRING(meta_value FROM 7), 
     SUBSTRING(meta_value, 4, 2), 
     SUBSTRING(meta_value, 1, 2));

Run that as a SELECT first to verifying the output before update and make sure it's what you're really looking for.

Answer 2

As the field is stored as varchar(255) in that table (that is, it's stored as a string, rather than a special date format), you'll need to slice it and then join the chunks together.

Assuming that the original date is dd/mm/YYYY and the required format is YYYYmmdd then you could do something like this:

UPDATE wp_postmeta SET meta_value = concat(substring(meta_value,7,4),substring(meta_value,4,2),substring(meta_value,1,2))

This is using the substring function to split the original string, the format of this command is:

substring(string to split,starting position in string, length of required substring)

This isn't the only format, more examples are here - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

Then we use the concat function to join the 3 new strings as one (details on that one here - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat)

But you'll need to be very careful running that update as it is. At the moment it will do that for every row in the table. So if you have a row with post_meta='Stuart Moore' it will be updated to 'MooarSt' which probably isn't what you want. So make sure you use a where clause to restrict which rows you're modifying.

Answer 3

If meta_value field type is a DATE then you could use

UPDATE wp_postmeta 
SET meta_value = DATE_FORMAT(meta_value, '%Y%m%d');

or if it is a string then use

UPDATE wp_postmeta 
SET meta_value = DATE_FORMAT(STR_TO_DATE(meta_value, '%d/%m/%Y'), '%Y%m%d');

More on MySQL date functions