Extracting date from text string

Question

Extracting date from text string

pjsweat

SSC Veteran

Points: 254
More actions
September 27, 2011 at 7:40 am

#97932

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

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

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

BrainDonor SSCoach Points: 19269 More actions · Answer 1

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.

Steve Hall
Linkedin
Blog Site

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

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/

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 3

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