-1

I am trying to extract the date from a string. I am using the below mentioned query using both SUBSTRING and PATINDEX:

SELECT Message, SUBSTRING(Message, PATINDEX('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%', Message), 10) as Month_Captured

I am confused by the results "CONTROL V", please see the attached image. Can you please explain why this is happening and what is the best way to extract the date from a string.

SQL QUERY RESULTS

Dale K
  • 25,246
  • 15
  • 42
  • 71
Yash
  • 319
  • 1
  • 4
  • 6
  • 1
    please read https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query and produce a [mre] without imnages as described here https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question – nbk Mar 17 '22 at 20:33
  • 1
    You're *picture* looks like SQL Server, your query looks like T-Sql, but you've tagged *MySql*. – Stu Mar 17 '22 at 20:38
  • 1
    also patindex only exist ion sql server mysql has no such function – nbk Mar 17 '22 at 20:39
  • I have updated the tags. – Bill Karwin Mar 17 '22 at 20:50
  • Hints: what does `SUBSTRING(Message, 0, 10)` return? How about `PATINDEX(«your pattern», Message)`? – Ben Thul Mar 17 '22 at 23:20

2 Answers2

0

The easiest way to handle a no-match case from CHARINDEX is to coerce the 0 to a null with the NULLIF(value, 0) function. This will in turn cause the SUBSTRING to also yield null.

DECLARE @Pattern VARCHAR(100) = '%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%'

SELECT
    Message,
    SUBSTRING(Message, NULLIF(PATINDEX(@Pattern, Message),0), 10) as Month_Captured
FROM Data
T N
  • 4,322
  • 1
  • 5
  • 18
0

Since you're after a valid date, you could wrap your existing criteria with try_convert that will either return a valid date or NULL

SELECT Message, 
  Try_Convert(date, 
    SUBSTRING(Message, PATINDEX('%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%', Message), 10)
  ) as Date_Captured
Stu
  • 30,392
  • 6
  • 14
  • 33