Search for dates inside of a text field

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?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I would try this way:
create a table with the "patterns" you are looking for, and join with that table ...
so, the table would be like:
pattern  [text]

with a value like this:

and then join that with your table:
select t.*
  from yourtable t
  inner join date_patterns p
     on t.text like p,pattern 

Open in new window

this shall solve also that for each different pattern matched, you will get 1 row returned ...
however, if the same pattern is present in the text more than once, you will not get more rows returned ...

swpa_wntAuthor 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:
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 EngineerCommented:
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

etc ... until

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 ...

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

swpa_wntAuthor 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"

swpa_wntAuthor Commented:
Or would I simply add "expiration" as a record in my patterns table?
swpa_wntAuthor 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 EngineerCommented:
>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
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
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.