Reading a date from the middle of a bigger string

  • Thread starter Maury Markowitz
  • Start date
M

Maury Markowitz

I'm reading a text file that embeds a date into a header line. They didn't
separate everything out nicely, the date (two of them actually) are embedded
in the middle of a long string.

Is there some simple way to pull it out? I don't know the exact location (it
changes). Is there anything like scanf, or does anyone have an example of
regexing it out?
 
L

Lynn Trapp

Without seeing an example it is pretty hard to say. Is there consistent
thing within the string that could be used to signify the beginning or
ending of the date portion of the string?
 
M

Maury Markowitz

Lynn Trapp said:
Without seeing an example it is pretty hard to say. Is there consistent
thing within the string that could be used to signify the beginning or
ending of the date portion of the string?
Click to expand...

CURRENTLY it looks like this...

"From 11/25/2004 to 11/26/2004"

However the format changes from day to day, the users are simply typing in
the data by hand. I need to extract at least the first date in the string,
and perhaps the second (as a check).
 
L

Lynn Trapp

CURRENTLY it looks like this...
"From 11/25/2004 to 11/26/2004"
Click to expand...

If the format is not going to be consistent, then it is going to be
extremely difficult to pull out. If the format is ALWAYS as you posted then
you could use something like this:

SELECT Mid([YourField],InStr([YourField]," ")+1,10) AS FirstDate,
Right([YourField],10) AS SecondDate
FROM YourTable;
 
B

Brendan Reynolds

If the users are typing in the data by hand, Maury, then there isn't any
*reliable* way to do what you want. You could use a regular expression to
search for two digits followed by a slash followed by two digits followed by
a slash followed by four digits. Great. Now what happens when a user decides
to use a two-digit year or to enter today's date as 7 Dec 2004, or to use
hyphens instead of slashes or ... well, I'm sure you get the picture.

If you still want to pursue the regular expression idea, though, take a look
at the following URL. The example assumes yyyy-mm-dd format, but there are
notes at the end about how to modify it for different formats ...

http://www.regular-expressions.info/dates.html

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
M

Maury Markowitz

Brendan Reynolds said:
If the users are typing in the data by hand, Maury, then there isn't any
*reliable* way to do what you want.
Click to expand...

I think it's safe to assume the format of the date itself will not change.
What will change is the position inside the string, they often add more text
to the start or end of the string.

Maury
 
Top