Running the code you quoted Jeff, I get these results.
sourcedata sourcedata2
12 Jan 2013 Test.docx 20130112 Test.docx
01 February 2001 File.pptx 20010201 File.pptx
Meeting 04 Feb 2012.xlsMeeting 20120204.xls
09 September 2011.txt 20110909.txt
30 Jan 13.doc 20130130.doc
There were some issues in my earlier attempt. Perhaps you were testing one of those.
Edit: Noting that I'm not particularly proud of my brute force attempt. Certainly could be improved.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St