I have a database with two columns which are related. One is birthdate and the other one is birthdatetr. A number of these in birthdate contains date " BC" but it needs to be replaced by "-" date

Birthdatetr contains the dates in true format like YYYY-MM-DD in numbers, the " BC" isn't there. The birthdates can have the following formats

It needs to become

The Abt, Bef and Aft birthdates are relatively easy to do, it's the 23 Oct 4004 BC which gives me headaches. It's just beyond my skill level

I tried this

update tng_people set deathdate = REPLACE(birthdate, ' 1', ' -1') where birthdate like " % BC"; update tng_people set deathdate = REPLACE(birthdate, ' 2', ' -1') where birthdate like " % BC"; update tng_people set deathdate = REPLACE(birthdate, ' 3', ' -1') where birthdate like " % BC";

and then

update tng_people set deathdate = REPLACE(birthdate, ' BC', '') where birthdate like " % BC";

However that would make 23 Oct 4004 BC look like 23 Oct -4004 which is weird

Anybody has better ideas?

CodePudding user response：

On recent versions of MariaDB, a regex replacement can be used here:

SELECT
    TRIM(REGEXP_REPLACE(birthdate,
                   '(.*?)(\\d{1,2} \\w{3} )?(\\d ) BC',
                   '\\1 -\\2\\3')) AS birthdate,
    birthdatetr
FROM tng_people;

Demo

Here is an explanation of the regex pattern:

• (.*?) match and capture in \1 any leading non date portion
• (\d{1,2} \\w{3} )? match and capture in \2 an optional day and 3 letter month
• (\d ) match and capture in \3 the numeric year
• BC match literal 'BC'

Then we replace with \1 -\2\3 to insert a hyphen in front of the start of the date (either short form or long form).

If you really want to update the birthdate column, then use:

UPDATE tng_people
SET birthdate = TRIM(REGEXP_REPLACE(birthdate,
                                    '(.*?)(\\d{1,2} \\w{3} )?(\\d ) BC',
                                    '\\1 -\\2\\3'))
WHERE birthdate LIKE '