Search for dates inside of a text field

Posted on 2011-04-21
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?
Question by:swpa_wnt
    LVL 142

    Accepted Solution

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


    Author Comment

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

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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 ...


    Author Comment

    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 Comment

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

    Author Comment

    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.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >Or would I simply add "expiration" as a record in my patterns table?
    exactly :)
    LVL 15

    Expert Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now