InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:51:18 GMT20http://www.sqlservercentral.com/Forums/Topic1424222-3077-1.aspx[quote][b]monilps (2/26/2013)[/b][hr]Hey,Thank you for quick responseWhat I meant was For Ex: Row1: aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsdsRow2: asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofnRow3: sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsnRow4: fdklnsdlnf 02-02 fjsksngjkRow5: sdkflsdnfl 02/02 hisfdhiif sdjfsdkfkSo I need result set as Row1: 02/20/2002Row2: 9/3/99Row3: 02-02-02Row4: 02-02Row5: 02/02Thanks,[/quote]the technique i posted will work for all rows, and even better, will also catch when a comment has multiple dates in the same string.get the function, modify my query to use your table and data, and let us know if it's doing what you expected.I made a best guess for your data, so i can provide an example, but if you want a working query, it's up to you to provide the CREATE TABLE definition and sample data for testing[code]With mySampledata(id,SomeComment)AS(SELECT 1,'- aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds' UNION ALLSELECT 2,'- asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn' UNION ALLSELECT 3,'- sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn' UNION ALLSELECT 4,'- fdklnsdlnf 02-02 fjsksngjk' UNION ALLSELECT 5,'- sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk')SELECT mySampledata.*,myfn.* ,Case when IsDate(myfn.Item)=1 THEN CONVERT(datetime,myfn.Item) ELSE NULL END AS PotentialDateFROM mySampledataCROSS APPLY dbo.DelimitedSplit8K(SomeComment,' ') Myfn[/code]Tue, 26 Feb 2013 13:41:43 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1424222-3077-1.aspxHey,Thank you for quick responseWhat I meant was For Ex: Row1: aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsdsRow2: asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofnRow3: sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsnRow4: fdklnsdlnf 02-02 fjsksngjkRow5: sdkflsdnfl 02/02 hisfdhiif sdjfsdkfkSo I need result set as Row1: 02/20/2002Row2: 9/3/99Row3: 02-02-02Row4: 02-02Row5: 02/02Thanks,Tue, 26 Feb 2013 13:05:57 GMTmonilpshttp://www.sqlservercentral.com/Forums/Topic1424222-3077-1.aspxit'll take a bit of work, and you'll need the DelimitedSplit8K function (search SSC and read the article)things like 02/02 and 02-02 are not dates, so they do not get caught in my example...you'll have to add additional pattern matching on the myfn.Item like i've done with the ISDATE function...something like 'LIKE [0-9][0-9]/[0-9][0-9] and other similar patterns.[code]With mySampledata(id,SomeComment)AS(SELECT 1,'- aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds- asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn- sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn- fdklnsdlnf 02-02 fjsksngjk- sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk')SELECT mySampledata.*,myfn.* ,Case when IsDate(myfn.Item)=1 THEN CONVERT(datetime,myfn.Item) ELSE NULL END AS PotentialDateFROM mySampledataCROSS APPLY dbo.DelimitedSplit8K(SomeComment,' ') Myfn[/code]Tue, 26 Feb 2013 13:00:37 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1424222-3077-1.aspxI need extract date from one column which has datatype text.For Ex: - aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds- asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn- sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn- fdklnsdlnf 02-02 fjsksngjk- sdkflsdnfl 02/02 hisfdhiif sdjfsdkfkTue, 26 Feb 2013 12:44:10 GMTmonilps