• 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?
0
swpa_wnt
Asked:
swpa_wnt
  • 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 ...

http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx
so, the table would be like:
date_patterns
pattern  [text]

with a value like this:
*[0-1][0-9][-][0-2][0-9][-][1-3][0-9][0-9][0-9]*
*[0-1][0-9][/][0-2][0-9][/][1-3][0-9][0-9][0-9]*
etc

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


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


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

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

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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Or would I simply add "expiration" as a record in my patterns table?
exactly :)
0
 
danrosenthalCommented:
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

0

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