We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Search for dates inside of a text field

swpa_wnt
swpa_wnt asked
on
Medium Priority
392 Views
Last Modified: 2012-05-11
In Access 2007, we have a text field in a table where users can type notes as needed.  Occasionally, these notes will mention one ore more dates.  My task is to run a query that will find date strings within the text field, and return the record in the query results for EACH DATE that is found.

Obviously, I won't be searching for actual date values; rather, I'll need to search for a variety of string formats ('mm/dd/yy'; 'mm-dd-yyyy', etc.).  What's the best syntax for doing that?

Also, as mentioned, if the text field for Record #123 contains two date strings, then I'll need Record #123 to appear in my query results twice.

Can anyone show me a good way to accomplish this?
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks, I appreciate the help.  I'm liking your approach of setting up the patterns in a table.

As it turns out, a text field with two date patterns somewhere in it will NOT produce two separate results in the query. For example, the following text field still only produces a single query result:

"Articles II, III, IV, V, VI, VII and Sec 2 of Art X expire 12/31/2013; Arts 1, VIII, IX, and Secs 1, 3, and 4 of Art X expire 12/31/2020."

Here's the query as I have it written:
-----------------------------
SELECT *
FROM CONTRACT INNER JOIN date_patterns ON CONTRACT.UniqueTerminationDesc like date_patterns.pattern;
-----------------------------


I may not actually need the query to produce two results for a field like this, however.  I'm just curious why/how it's only producing a single result.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
because the join finding 1 or many matches (for the same pattern) will only return 1 record.

which is exactly what I feared. to workaround, you could add more patterns that would match different "dates", for example


*[0-1][0-9][/][1-2][0][/][1-3][0-9][0-9][0-9]*
*[0-1][0-9][/][0-2][1][/][1-3][0-9][0-9][0-9]*
*[0-1][0-9][/][0-2][2][/][1-3][0-9][0-9][0-9]*
*[0-1][0-9][/][0-2][3][/][1-3][0-9][0-9][0-9]*
etc ... until
*[0-1][0-9][/][0-2][9][/][1-3][0-9][0-9][0-9]*

however, if you have 2 dates with the same "day" (for example, 12/31/2010  and 01/01/2011 would result in 1 single match ...

Author

Commented:
Hm, that may be going overboard on the patterns!

Is there also a way to do an OR inside a LIKE expression, such as:

INNER JOIN date_patterns ON CONTRACT.Description LIKE date_patterns.pattern OR LIKE "expiration"

?

Author

Commented:
Or would I simply add "expiration" as a record in my patterns table?

Author

Commented:
Duh, I just added *expir* to the Date_Patterns table.

Thanks for your help.  As I try to fine-tune this thing to work with my report, I may have other questions, but those will be posted separately.

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>Or would I simply add "expiration" as a record in my patterns table?
exactly :)
Looks like you already have what you were looking for, but I took a pass at the challenge of finding second matches.

Finding third and further matches can probably be acheived by a few tweaks and adding some sort of WHILE loop.
create table Contract(
	cID INT identity(1,1)
	,UniqueTerminationDesc NVARCHAR(500)
)

create table date_patterns(
	pID INT identity(1,1)
	,pattern NVARCHAR(100)
	,patlen int
)

insert into contract select 'Articles II, III, IV, V, VI, VII and Sec 2 of Art X expire 12/31/2013; Arts 1, VIII, IX, and Secs 1, 3, and 4 of Art X expire 12/31/2020.'
insert into contract select 'not much'
insert into contract select 'Today is 4/22/2011'
insert into contract select 'Today is 04/22/2011. More text'

insert into date_patterns select '%[0-1][0-9][-][0-3][0-9][-][1-3][0-9][0-9][0-9]%',10
insert into date_patterns select '%[0-1][0-9][/][0-3][0-9][/][1-3][0-9][0-9][0-9]%',10
insert into date_patterns select '%[0-9][-][0-3][0-9][-][1-3][0-9][0-9][0-9]%',9
insert into date_patterns select '%[0-9][/][0-3][0-9][/][1-3][0-9][0-9][0-9]%',9

-- Initial search
SELECT DISTINCT c.cID, MIN(p.pID) AS pID, 0 AS start, c.UniqueTerminationDesc
INTO #Result
FROM CONTRACT c
INNER JOIN date_patterns p
ON c.UniqueTerminationDesc like p.pattern
GROUP BY c.cID, c.UniqueTerminationDesc 

-- search for a second date
INSERT INTO #result (cid,pid,start,UniqueTerminationDesc)
SELECT DISTINCT c.cID, MIN(p2.pID) AS pID, PATINDEX(p.pattern,c.UniqueTerminationDesc) + p.patlen
, SUBSTRING(c.UniqueTerminationDesc,PATINDEX(p.pattern,c.UniqueTerminationDesc) + p.patlen,LEN(c.UniqueTerminationDesc)-PATINDEX(p.pattern,c.UniqueTerminationDesc) + p.patlen)
From #Result r
INNER JOIN CONTRACT c
ON c.cid = r.cid
INNER JOIN date_patterns p
ON p.pID = r.pID
INNER JOIN date_patterns p2
ON SUBSTRING(c.UniqueTerminationDesc,PATINDEX(p.pattern,c.UniqueTerminationDesc) + p.patlen,LEN(c.UniqueTerminationDesc)-PATINDEX(p.pattern,c.UniqueTerminationDesc) + p.patlen) like p2.pattern
WHERE LEN(c.UniqueTerminationDesc) > PATINDEX(p.pattern,c.UniqueTerminationDesc) + p.patlen - 1
GROUP BY c.cID, p.pattern, c.UniqueTerminationDesc, p.patlen

-- search results
select r.cid, SUBSTRING(r.UniqueTerminationDesc,PATINDEX(p.pattern,r.UniqueTerminationDesc),p.patlen) AS datefound
from #result r
INNER JOIN date_patterns p
ON p.pID = r.pID

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.