Extracting date from text string

  • I am trying to pull out the date '08/10/2011 from the below

    text column into a seperate field -

    'Your item was delivered at 12:59 PM on 08/10/2011 in MARTINEZ,CA 94553.'

    Any thoughts on the most efficient way to handle this?

    Len appears to always be 10 for the date

  • Have a look at String Functions in MSDN http://msdn.microsoft.com/en-us/library/aa258891(v=sql.80).aspx.

    I would probably use PATINDEX to locate the first '/', you know the date starts 2 characters before this and then locate the space character after this (as you say the date 'appears to be 10' - if you aren't certain then don't assume). Use SUBSTRING to extract the string.

    Have a play with the string functions and see what you can do.

  • pjsweat (9/27/2011)


    I am trying to pull out the date '08/10/2011 from the below

    text column into a seperate field -

    'Your item was delivered at 12:59 PM on 08/10/2011 in MARTINEZ,CA 94553.'

    Any thoughts on the most efficient way to handle this?

    Len appears to always be 10 for the date

    I'd probably do this with a RegEx CLR, which would allow for better date validation. But you could do it with T-SQL like this

    --Some test data first

    DECLARE @TABLE AS TABLE (deliveryText VARCHAR(200))

    INSERT INTO @TABLE

    SELECT 'Your item was delivered at 12:59 PM on 08/10/2011 in MARTINEZ,CA 94553.'

    UNION ALL SELECT 'Your item was delivered at 12:59 PM on 12/31/2011 in somewhere.'

    UNION ALL SELECT 'Your item was delivered at 12:59 PM on 01/12/2011 in somewhere.'

    --Actual query

    SELECT RTRIM(LTRIM(Substring(deliveryText, start, length - start - fromEnd + 2))) AS [extracted]

    FROM (

    SELECT MAX(PATINDEX([Matched], deliveryText)) AS start

    ,MAX(PATINDEX(ReverseMatch, reverse(deliveryText + ' ')) - 1) AS fromEnd

    ,len(deliveryText + '|') - 1 AS [length]

    ,deliveryText AS deliveryText

    FROM (

    VALUES (

    '% [0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9] %'

    ,'% [0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1] %'

    )

    ) AS f([Matched], ReverseMatch)

    CROSS JOIN @TABLE

    GROUP BY deliveryText

    ) search

    --EDIT--

    Ack! Just noticed this is the SQL Server 2005 forum! 2005 syntax below.

    SELECT RTRIM(LTRIM(Substring(deliveryText, start, length - start - fromEnd + 2))) AS [extracted]

    FROM (

    SELECT MAX(PATINDEX([Matched], deliveryText)) AS start

    ,MAX(PATINDEX(ReverseMatch, reverse(deliveryText + ' ')) - 1) AS fromEnd

    ,len(deliveryText + '|') - 1 AS [length]

    ,deliveryText AS deliveryText

    FROM (

    SELECT '% [0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9] %'

    ,'% [0-9][0-9][0-9][1-2]/[0-9][0-3]/[0-9][0-1] %'

    ) AS f([Matched], ReverseMatch)

    CROSS JOIN @TABLE

    GROUP BY deliveryText

    ) search


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi,

    This code will run only if you are sure that "/" won't appear prior to Date.

    Declare @A as nvarchar(200)

    Set @A = 'Your item was delivered at 12:59 PM on 08/09/2011 in MARTINEZ,CA 94553'

    SELECT SUBSTRING(@A, CHARINDEX('/',@A)-2,10)

    regards

    Palash Gorai

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply