Tsql: Find A Date With Varying Characters In A String

I need to find a continuous date in a string from column name Filename. The string has other numbers in it with dashes(or another character, like an underscore), but I only need th

Solution 1:

Try it like this:

DROPTABLE #StuID
GO
CREATETABLE #StuID (
 FILENAME VARCHAR(MAX)
,StudentID INT
)

INSERTINTO #StuID
( FILENAME  )
VALUES
 ('Smith John D, 11-23-1980, 1234567.pdf')
,('Doe Jane, _01_22_1980_123456.pdf')
,('John Doe, 567891.pdf' );

WITH Casted([FileName],ToXml) AS
(
    SELECT [FILENAME] 
          ,CAST('<x>'+ REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([FILENAME],'.',' '),'-',' '),'_',' '),',',' '),' ','</x><x>') +'</x>'AS XML)
    FROM #StuID
)
SELECT [FileName] 
      ,numberFragments.value('/x[.>=1 and .<=31][1]','int') AS MonthFragment --using <=12 might bring back the second fragment twice...
      ,numberFragments.value('/x[.>=1 and .<=31][2]','int') AS DayFragment
      ,numberFragments.value('/x[.>=1960 and .<=2050][1]','int') AS YearFragment
      ,numberFragments.value('/x[.>=100000 and .<=10000000][1]','int') AS StudId
FROM Casted
CROSS APPLY (SELECT ToXml.query('/x[not(empty(. cast as xs:int?))]')) A(numberFragments);

The idea in short:

As in the previous answer we will break the string to a XML and filter for fragments castable to int. The magic ist the XQuery-filtering:

• We pick the first fragment between 1 and 31, which is the month hopefully
• We pick the second fragment between 1 and 31 which is the day hopefully
• We pick the first fragment between 1960 and 2050 which is the year hopefully
• And we pick the student's id, which is the first fragment between 100000 and 10000000.

Hint: It looks like a nice idea to use <=12 for the month fragment, but I'd use the same filter for day and month to make sure, that we pick the first and the second fragment of the same value region...

Solution 2:

I don't think you have the pattern quite right. Also, you can use a CASE expression to return NULL:

SELECT FILENAME,
       (CASEWHEN FileName LIKE'%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%'THENsubstring(FileName, patindex('%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%', FileName), 10)
        END) AS dob
FROM #dob;

You can also dispense with the CASE and use NULLIF():

substring(FileName, NULLIF(patindex('%[0-9][0-9][-_][0-9][0-9][-_][0-9][0-9][0-9][0-9]%', FileName), 0), 10) as dob

Solution 3:

Another method would be (after using PATINDEX to find the date) is force to string's format to MM/dd/yyyy and then use an explicit style for the conversion:

SELECT*,
       TRY_CONVERT(date,STUFF(STUFF(SUBSTRING(d.FILENAME,V.I, 10),3,1,'/'),6,1,'/'),101)
FROM #dob d
     CROSS APPLY (VALUES(NULLIF(PATINDEX('%[0-9][0-9]_[0-9][0-9]_[0-9][0-9][0-9][0-9]%',d.[FILENAME]),0))) V(I);