Why is the PATINDEX returning wrong results?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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