MySQL – replace part of strings ending with '/'

Question

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.

Gordon Linoff · Accepted Answer · 2018-05-05 22:08:45Z

2

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))

answered May 5, 2018 at 22:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

HynekS Over a year ago

Yes, it works! Thank you very much. But I still wonder why the REPLACE(year, '%/', '20') did not work…

Gordon Linoff Over a year ago

@Hynek . . . Because your strings do not have a percent sign in them. As I explained, replace() does not support wildcards.

Collectives™ on Stack Overflow

MySQL – replace part of strings ending with '/'

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related