CODEWITHSUNDEEP
mysqlsqlreplacephpmyadmin
HynekS
HynekS

Reputation: 3327

MySQL – replace part of strings ending with '/'

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

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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

Related Questions