July 11, 2011 at 12:10 pm
I'm trying to find the best way to do a pattern search on a VARCHAR field that may or may not have 1 or more date fields in it without looking at CLR integration (and custom coding via CLR) and I'm having trouble.
WHat I'd like is something that will return a 0 or 1 (or any dicernable Boolena value to indicate true or false result) if the TABLE.Column searched contains a recognizable date. I can write a query to locate a specifc date like '2011/07/12' but trying to come up with some code that will return true (as well as where in the string teh date starts and how long it is , number of characters) such as the bvelow examples is proving to be impossible.
Valid Dates that if encountered should return true: All are exmaples of January 1, 2012.
2011/01/01 - yyyy/mm/dd
01/01/2011 - mm/dd/yyyy
01/01/11 - mm/dd/yy
11/01/01 - yy/mm/dd
1/1/11 - m/d/yy (this one is particularly tricky)
Is there anything within T-SQL thats can at least do most of these or am I going to have to look at CLR intergation and use a CLR function?
Kindest Regards,
Just say No to Facebook!July 11, 2011 at 12:24 pm
Have you tried?
ISDATE(yourColumn) = 1
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 11, 2011 at 2:19 pm
Jason Selburg (7/11/2011)
Have you tried?ISDATE(yourColumn) = 1
Jason,
I think the issue is that the date is contained within a staing like:
"On 01/01/2012 the new year will start"
So the query needs to parse the string and find the a date in the column.
If that is how the data looks you might be able to something with a numbers/tally table doing the string split and then applying ISDATE to the split strings. Something like this:
DECLARE @test-2 TABLE
(
id INT IDENTITY(1, 1),
string_with_date VARCHAR(100)
)
INSERT INTO @test-2
(string_with_date)
VALUES
('TEsting 1/1/2011 string'),
('TEsting 1/11/2011 string'),
('TEsting 01/11/2011 string'),
('TEsting string'),
('2011/01/01 testing another format')
SELECT
*,
ISDATE(DSK.Item) AS is_a_date
FROM
@test-2 AS T CROSS APPLY
dbo.DelimitedSplit8K(T.string_with_date, ' ') AS DSK
dbo.DelimitedSplit8K is taken from Jeff Moden's most recent Tally Table article[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 21, 2011 at 9:41 am
Thanks Jack, thats what I was looking for.
Kindest Regards,
Just say No to Facebook!July 21, 2011 at 10:19 am
Jack
Unfortunately that iTVF did not work for me with the string of text I am parsing. I was hoping that perhaps it was just because I am not seeing some gloarringly obvious change or item that if alktered would let me use Jeff's function.
Here is a sample string of text with a date in it:
'&MyDate=06/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'
Now if I place space charcters on both sides of the above date (06/01/2011) then the iTVF will find it and indetofy itcoprrectly but both sides of the date has to have a sapce or at least the same character so I could put an '=' on both sides and that woudl work. The problem there is the string is a concatenation of 1 or more Item=Value sets similiar to what I believe is called a PropertyBag ion VB programming. Forgive me if I have the VB terminology wrong.
Thoughts? I did think about doing a replace on all the '&' characters but the name=value pait uses both the '&' and the '=' and so I'd have to do 2 replaces and that may be the answer but I wanted to ask you first.
Thanks again
Kindest Regards,
Just say No to Facebook!July 21, 2011 at 10:53 am
Only a slight bit more "bulky" ...
DECLARE @test-2 TABLE
(
id INT IDENTITY(1, 1),
string_with_date VARCHAR(100)
)
INSERT INTO @test-2
(string_with_date)
VALUES
('&MyDate=06/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=06/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=06/1/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=06/1/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/1/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/1/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/1/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/1/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/30/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=1989/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=2011/12/30&MyEntity=laf&Accts=0&LandscapeMode=N'),
-- invalid matches
('&MyDate=9/9/9&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=199/91/11&MyEntity=laf&Accts=0&LandscapeMode=N')
SELECT
*,
CASE WHEN
string_with_date like '%[0-9]/[0-3][0-9]/[0-9][0-9]%'
OR
string_with_date like '%[0-9]/[0-9]/[0-9][0-9]%'
OR
string_with_date like '%[0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]%'
OR
string_with_date like '%[0-9]/[0-9]/[1-2][0-9][0-9][0-9]%'
THEN 'Yes'
ELSE 'No' END
FROM
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 21, 2011 at 11:27 am
YSLGuru (7/21/2011)
JackUnfortunately that iTVF did not work for me with the string of text I am parsing. I was hoping that perhaps it was just because I am not seeing some gloarringly obvious change or item that if alktered would let me use Jeff's function.
Here is a sample string of text with a date in it:
'&MyDate=06/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'
Now if I place space charcters on both sides of the above date (06/01/2011) then the iTVF will find it and indetofy itcoprrectly but both sides of the date has to have a sapce or at least the same character so I could put an '=' on both sides and that woudl work. The problem there is the string is a concatenation of 1 or more Item=Value sets similiar to what I believe is called a PropertyBag ion VB programming. Forgive me if I have the VB terminology wrong.
Thoughts? I did think about doing a replace on all the '&' characters but the name=value pait uses both the '&' and the '=' and so I'd have to do 2 replaces and that may be the answer but I wanted to ask you first.
Thanks again
How about a double call to the function like this:
DECLARE @test-2 TABLE
(
id INT IDENTITY(1, 1),
string_with_date VARCHAR(100)
)
INSERT INTO @test-2
(string_with_date)
VALUES
('&MyDate=06/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=06/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=06/1/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=06/1/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/1/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/1/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/01/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/1/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=12/1/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=6/30/2011&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=1989/01/11&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=2011/12/30&MyEntity=laf&Accts=0&LandscapeMode=N'),
-- invalid matches
('&MyDate=9/9/9&MyEntity=laf&Accts=0&LandscapeMode=N'),
('&MyDate=199/91/11&MyEntity=laf&Accts=0&LandscapeMode=N')
SELECT
*,
ISDATE(DSK.Item) AS is_a_date,
ISDATE(DSK2.Item) AS is_a_date2
FROM
@test-2 AS T CROSS APPLY
dbo.DelimitedSplit8K(T.string_with_date, '&') AS DSK CROSS APPLY
dbo.DelimitedSplit8K(DSK.Item, '=') AS DSK2
I did find one issue using the test data Jason provided. The IsDate function considers '9/9/9' a valid date and if you put that into a datetime variable it returns as 2009/09/09. Also Jason's solution will probably scale better. I don't know if it will be easier to maintain because of all the permatations of dates. What if someone using 'Jan 01, 2001' or '01-JAN-01'. IsDate would likely handle these better without having to cover every possibility in the CASE statement.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply