September 27, 2011 at 7:40 am
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
September 27, 2011 at 7:53 am
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.
September 27, 2011 at 8:12 am
pjsweat (9/27/2011)
I am trying to pull out the date '08/10/2011 from the belowtext 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
September 27, 2011 at 8:13 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy