• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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?
  • 4
  • 3
1 Solution
Guy Hengel [angelIII / a3]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 ...

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now