Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

extract date Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 12:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 3:10 PM
Points: 42, Visits: 165
I 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 sdjfsdkfk
Post #1424222
Posted Tuesday, February 26, 2013 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
it'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.
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 PotentialDate
FROM mySampledata
CROSS APPLY dbo.DelimitedSplit8K(SomeComment,' ') Myfn



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1424226
Posted Tuesday, February 26, 2013 1:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 14, 2014 3:10 PM
Points: 42, Visits: 165
Hey,

Thank you for quick response
What I meant was
For Ex:
Row1: aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds
Row2: asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn
Row3: sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn
Row4: fdklnsdlnf 02-02 fjsksngjk
Row5: sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk

So I need result set as
Row1: 02/20/2002
Row2: 9/3/99
Row3: 02-02-02
Row4: 02-02
Row5: 02/02

Thanks,
Post #1424229
Posted Tuesday, February 26, 2013 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
monilps (2/26/2013)
Hey,

Thank you for quick response
What I meant was
For Ex:
Row1: aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds
Row2: asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn
Row3: sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn
Row4: fdklnsdlnf 02-02 fjsksngjk
Row5: sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk

So I need result set as
Row1: 02/20/2002
Row2: 9/3/99
Row3: 02-02-02
Row4: 02-02
Row5: 02/02

Thanks,


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
With mySampledata(id,SomeComment)
AS
(SELECT 1,'- aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds' UNION ALL
SELECT 2,'- asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn' UNION ALL
SELECT 3,'- sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn' UNION ALL
SELECT 4,'- fdklnsdlnf 02-02 fjsksngjk' UNION ALL
SELECT 5,'- sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk')
SELECT mySampledata.*,
myfn.* ,
Case when IsDate(myfn.Item)=1 THEN CONVERT(datetime,myfn.Item) ELSE NULL END AS PotentialDate
FROM mySampledata
CROSS APPLY dbo.DelimitedSplit8K(SomeComment,' ') Myfn



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1424241
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse