dwain.c (9/29/2013)

---

Ok. This should work.

But someone I'm sure will probably come up with a better way.

WITH SampleData (sourcedata) AS

(

SELECT '12 Jan 2013 Test.docx'

UNION ALL SELECT '01 February 2001 File.pptx'

UNION ALL SELECT 'Meeting 04 Feb 2012.xls'

UNION ALL SELECT '09 September 2011.txt'

UNION ALL SELECT '30 Jan 13.doc'

)

SELECT sourcedata, sourcedata2=STUFF(sourcedata, pos1, (pos2+4)-pos1

,yy + COALESCE(m1, m2, m3, m4, m5, m6, m7, m8, m9, m10, m11, m12) + dd)

FROM SampleData a

CROSS APPLY

(

SELECT pos1=PATINDEX('%[0-9][0-9]%', sourcedata)

,x=CASE PATINDEX('%[0-9][0-9][0-9][0-9]%', sourcedata) WHEN 0 THEN 2 ELSE 4 END

) b

CROSS APPLY

(

SELECT pos2=CASE x

WHEN 4 THEN PATINDEX('%[0-9][0-9][0-9][0-9]%', sourcedata)

ELSE PATINDEX('%[0-9][0-9]%', RIGHT(sourcedata, LEN(sourcedata)-(pos1+1))) END

) c

CROSS APPLY

(

SELECT dd=SUBSTRING(sourcedata, pos1, 2)

,yy=CASE x WHEN 4 THEN SUBSTRING(sourcedata, pos2, x)

ELSE '20' + SUBSTRING(sourcedata, 2+pos2, x)

END

) d

CROSS APPLY

(

SELECT m1=RIGHT(100+NULLIF(1*SIGN(CHARINDEX('Jan', sourcedata)), 0), 2)

,m2=RIGHT(100+NULLIF(2*SIGN(CHARINDEX('Feb', sourcedata)), 0), 2)

,m3=RIGHT(100+NULLIF(3*SIGN(CHARINDEX('Mar', sourcedata)), 0), 2)

,m4=RIGHT(100+NULLIF(4*SIGN(CHARINDEX('Apr', sourcedata)), 0), 2)

,m5=RIGHT(100+NULLIF(5*SIGN(CHARINDEX('May', sourcedata)), 0), 2)

,m6=RIGHT(100+NULLIF(6*SIGN(CHARINDEX('Jun', sourcedata)), 0), 2)

,m7=RIGHT(100+NULLIF(7*SIGN(CHARINDEX('Jul', sourcedata)), 0), 2)

,m8=RIGHT(100+NULLIF(8*SIGN(CHARINDEX('Aug', sourcedata)), 0), 2)

,m9=RIGHT(100+NULLIF(9*SIGN(CHARINDEX('Sep', sourcedata)), 0), 2)

,m10=RIGHT(100+NULLIF(10*SIGN(CHARINDEX('Oct', sourcedata)), 0), 2)

,m11=RIGHT(100+NULLIF(11*SIGN(CHARINDEX('Nov', sourcedata)), 0), 2)

,m12=RIGHT(100+NULLIF(12*SIGN(CHARINDEX('Dec', sourcedata)), 0), 2)

) e;