Reputation: 3327
I have very little experience with SQL. I am stuck with seemingly very simple task – I've copied a row with contract numbers, which includes a two-digit year number (EG 1/14, 31/15, 221/17) into a new "year" row.
Now I need to replace the digits left from the forward slash and slash itself with '20' string to get pretty full year (EG 2014, 2015, 2017). The row is VARCHAR.
I was trying this:
UPDATE contracts SET year = REPLACE(year, '%/', '20')
or this:
UPDATE contracts SET year = REPLACE(year, '*/', '20')
Which did not throw error, but did not update anything (0 rows affected…).
I was trying even regexp, but this throws error, so obviously the syntax must be wrong:
UPDATE contracts SET year = REPLACE(year, '.+/\', '20')
Any help would be appreciated.
Upvotes: 0
Views: 194
Reputation: 1271003
MySQL does not support regular expressions with replace()
. Based on your sample data, you can do:
UPDATE contracts
SET year = CONCAT('20', SUBSTRING_INDEX(year, '/', -1))
Upvotes: 2