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 TABLE
(
id INT IDENTITY(1, 1),
string_with_date VARCHAR(100)
)
INSERT INTO @test
(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 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 TABLE
(
id INT IDENTITY(1, 1),
string_with_date VARCHAR(100)
)
INSERT INTO @test
(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
@test
______________________________________________________________________
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 TABLE
(
id INT IDENTITY(1, 1),
string_with_date VARCHAR(100)
)
INSERT INTO @test
(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 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 7 (of 7 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