0

I'm having trouble running MySQL REGEXP_SUBSTR within STR_TO_DATE to extract a date from a text field.

If can run the following query, and use REGEXP_SUBSTR to properly extract the date string.

SELECT REGEXP_SUBSTR('12/9/97 - Ependymoma!', '[0-9]{1,2}/[0-9]{1,2}/[0-9]{1,2}');
# '12/9/97'

If I hard code the date string in STR_TO_DATE, I can properly convert the date string to a DATE.

SELECT STR_TO_DATE('12/9/97', '%m/%d/%y');
# '1997-12-09'

However, if I call the REGEXP_SUBSTR within STR_TO_DATE it returns NULL.

SELECT STR_TO_DATE(REGEXP_SUBSTR('12/9/97 - Ependymoma!', '[0-9]{1,2}/[0-9]{1,2}/[0-9]{1,2}'), '%m/%d/%y');
# NULL

I've been struggling with this. Any direction would be appreciated. Thanks.

jayurbain
  • 429
  • 1
  • 5
  • 11
  • 1
    Not reproduced. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bd4d211261efb683d718f984e3bb388f – Akina Feb 18 '21 at 13:56
  • 1
    I also tested on a local instance of MySQL 8.0 and it works. It does not return NULL. What does `SELECT VERSION();` return for you? – Bill Karwin Feb 18 '21 at 14:20
  • SELECT VERSION() -> 8.0.12, running on Mac. Thanks. – jayurbain Feb 18 '21 at 15:48
  • Try selecting the date into a local variable first `SELECT @tmp := REGEXP_SUBSTR('12/9/97 - Ependymoma!', '[0-9]{1,2}/[0-9]{1,2}/[0-9]{1,2}')` `SELECT STR_TO_DATE(@tmp, '%m/%d/%y')` – James Stevens Feb 18 '21 at 17:42

2 Answers2

1

Thanks to everyone for all of the help! Several commenters had no problems with my code, but I could not get this to work in my recently installed version of MySQL. I have to get this work done, so I switched to PostgreSQL and had no problems.

jayurbain
  • 429
  • 1
  • 5
  • 11
0

Try selecting the date into a local variable first

SELECT @tmp := REGEXP_SUBSTR('12/9/97 - Ependymoma!', '[0-9]{1,2}/[0-9]{1,2}/[0-9]{1,2}')
SELECT STR_TO_DATE(@tmp, '%m/%d/%y');

Also posted as an answer cos I couldn't get the comment to show two lines, so it was a mess

BTW: your original combined statement works fine for 10.4.8-MariaDB

James Stevens
  • 374
  • 2
  • 8
  • Thanks. I tried the local variable and that did not work. Weird. the dbfiddle from Akina also worked. – jayurbain Feb 18 '21 at 17:48
  • that's VERY odd, considering the two statements work separately, with constants. I wonder if it's something to do with converting character sets? What happens if you add `select @tmp` in between the two? Also, when you say `it didn't work` what happened, just `NULL` again? – James Stevens Feb 18 '21 at 18:14
  • 1
    Yes, by not working it just returned NULL. I've tried both the command line and within MySQL workbench and no go. Using @tmp in between did not work either. Thanks everyone for all your help! I tried the equivalent operation with PostgreSQL and had no problems. – jayurbain Feb 19 '21 at 19:28