Extract date from string t-sql

Question

I'm using SQL Server (T-SQL) 2016 and I would like to find the best vay to extract date from string. I dont know date starting index but I know that in given string there will be only one occurence.

DECLARE @myString AS NVARCHAR(max) = 'test string with 1/1/2023 date'
SELECT Substring(@myString, PATINDEX('[0-9]*/[0-9]*/[0-9]*', @myString), 8)

This one will work no matter where date starts but it assumes that date is 8 character, but if it would be 12/12/2023 then it will now work. Text may be changed with the time but format of date will remain the same. So in other words I need a code to extract date of given format from string. What is the best way to do that in t-sql?

• Akina: PATINDEX performs not regular expressions search but simplified search only. See stackoverflow.com/search?q=regular+expression+%5BSQL-Server%5D

Answer 1

If you are using SQL 2016 or later, I would use this method. It's fairly fast and requires minimal string manipulation. I use string split on the space character, then only return the results that can be coerced to a date. This has the advantage that if there are multiple dates then they will all come back (although you said there is only ever one occurrence of a datelike string).

If you don't have STRING_SPLIT, then there are methods for doing the equivalent without it, just search around.

DECLARE @myString AS NVARCHAR(max) = 'test string with 1/1/2023 date'
SELECT P.[value]
FROM STRING_SPLIT(@myString, ' ') AS P
WHERE TRY_CONVERT(DATE, P.[value]) IS NOT NULL