how to update a VARCHAR column value with RegEx?

I need to update the values from a VARCHAR column in a MySQL database from YYMMDDSXXXXX to YYMMDDSXXXX, where YY is year (i.e. 11 for 2011), MM is month (i.e. 09 for September), DD is day (i.e. 15), S is an one-digit order number (1 to 0) and XXXXX is a sequential number from 00001 to 99999.

I need to reduce ten-fold the sequential number, which should go from 0001 to 9999.

I thought about something like:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

But I'm not very good with MySQL, so I'm not sure how to do it. Can someone help?

Thanks in a开发者_开发百科dvance!

Regex replace can be implemented easily using this:

https://launchpad.net/mysql-udf-regexp

The regex you will need (in perl/posix std notation e.g. with sed)

s/([0-9]{7})0-9/$1$2/g

That is assuming you want to shorten the counter from the left (most significant) to keep the counts you have already unique (and obviously if you don't need that many places you want to remove the useless - most significant - bit).

That should get you well on your way, have fun :)