How to search for a substring?

wrote in message

> Hi,
>
>
> I'm fairly new at Filemaker, but I'm glad I dumped FoxPro for it.
> Except for this:
>
> I've exported all my Entourage emails into a database called
> "Email.fp7", with fields called "To", "From", "Body", Date Sent",
> etc.
>
> But some of the messages were undeliverable. I want to locate the
> people I sent those messages "To: " and put their name or e-mail
> address in a field so I can stop sending them messages.
>
> I'm trying to use a calculation to search for the line that says, "To:
> Fred Flintstone " but I can't figure out
> how to use the LEFT, LeftWord, POSITION or other function to search
> for the recipient's name.
>
> In FoxPro, there was a SUBSTRING() function, and I think I could have
> used that. Is there some way to create a calculation to seach for the
> recipient's name in a text field?
>
> Thanks,
>
> RH


Unless you've literally got hundreds / thousands of email addresses to
process, it's probably easier to Find and delete the unwanted records
manually, but ...

The Position function returns the number of characters into the text that
the seacrh text was found (or 0 if it wasn't found). This means you can find
where the "To:" text is with

Position ("To: "; TextField; 1; 1)

which tells FileMaker to find the text "To: " in the TextField's data,
starting at the first character and stopping when it finds the first
occurence.

Using this you can find the start and end of the email address by looking
for the "<" and ">" characters, again using the Position function, but
starting at the location the "To: " text was found.
eg.
"<" is at Position ("<"; TextField; Position("To: "; TextField; 1; 1);
1)
ie. find the first "<" after the "To: "

"<" is at Position (">"; TextField; Position("To: "; TextField; 1; 1);
1)
ie. find the first ">" after the "To: "

This means the email address will be found between these two and can be
extracted using the Middle function.
eg.
Middle (TextField;
Position ("<"; TextField; Position("To: "; TextField; 1; 1);
1) + 1;
Position (">"; TextField; Position("To: "; TextField; 1;
1) - Position ("<"; TextField; Position("To: "; TextField; 1; 1); 1) - 1; 1)
)

This tells FileMaker to extract some characters from the TextField's data,
starting at the character just after the "<" (ie. + 1) and taking the
correct number of character necessary to reach just before the ">" (ie. the
difference between where the ">" is minus where the "<" is, minus 1 more for
the "<" itself).

BUT,
this does assume that the first "To: " text is preceding the email address
and is not simply part of the subject for example.


NOTE: Being stuck on another computer to get Internet access means I haven't
got FileMaker handy, so some of these functions may have their parameters in
the wrong order. They also haven't been tested (I have tested the basic
formula in Excel though), so may need tweaking. (


Helpful Harry
Approaching four weeks of hopeless Internet Provider problems. xo(
 >> Stay informed about: How to search for a substring? 

Hi Harry,

With a little tweeking, your formula worked! When you broke it down in
pieces, it helped me understand what the functions were doing. Very
clever. Thank you very much.

And Christoph, thanks for your input, too.

RH

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


On Nov 24, 12:57 am, "Your Name" wrote:
> wrote in message
>
>
>
>
>
> > Hi,
>
> > I'm fairly new at Filemaker, but I'm glad I dumped FoxPro for it.
> > Except for this:
>
> > I've exported all my Entourage emails into a database called
> > "Email.fp7", with fields called "To", "From", "Body", Date Sent",
> > etc.
>
> > But some of the messages were undeliverable. I want to locate the
> > people I sent those messages "To: " and put their name or e-mail
> > address in a field so I can stop sending them messages.
>
> > I'm trying to use a calculation to search for the line that says, "To:
> > Fred Flintstone " but I can't figure out
> > how to use the LEFT, LeftWord, POSITION or other function to search
> > for the recipient's name.
>
> > In FoxPro, there was a SUBSTRING() function, and I think I could have
> > used that. Is there some way to create a calculation to seach for the
> > recipient's name in a text field?
>
> > Thanks,
>
> > RH
>
> Unless you've literally got hundreds / thousands of email addresses to
> process, it's probably easier to Find and delete the unwanted records
> manually, but ...
>
> The Position function returns the number of characters into the text that
> the seacrh text was found (or 0 if it wasn't found). This means you can find
> where the "To:" text is with
>
> Position ("To: "; TextField; 1; 1)
>
> which tells FileMaker to find the text "To: " in the TextField's data,
> starting at the first character and stopping when it finds the first
> occurence.
>
> Using this you can find the start and end of the email address by looking
> for the "<" and ">" characters, again using the Position function, but
> starting at the location the "To: " text was found.
> eg.
> "<" is at Position ("<"; TextField; Position("To: "; TextField; 1; 1);
> 1)
> ie. find the first "<" after the "To: "
>
> "<" is at Position (">"; TextField; Position("To: "; TextField; 1; 1);
> 1)
> ie. find the first ">" after the "To: "
>
> This means the email address will be found between these two and can be
> extracted using the Middle function.
> eg.
> Middle (TextField;
> Position ("<"; TextField; Position("To: "; TextField; 1; 1);
> 1) + 1;
> Position (">"; TextField; Position("To: "; TextField; 1;
> 1) - Position ("<"; TextField; Position("To: "; TextField; 1; 1); 1) - 1; 1)
> )
>
> This tells FileMaker to extract some characters from the TextField's data,
> starting at the character just after the "<" (ie. + 1) and taking the
> correct number of character necessary to reach just before the ">" (ie. the
> difference between where the ">" is minus where the "<" is, minus 1 more for
> the "<" itself).
>
> BUT,
> this does assume that the first "To: " text is preceding the email address
> and is not simply part of the subject for example.
>
> NOTE: Being stuck on another computer to get Internet access means I haven't
> got FileMaker handy, so some of these functions may have their parameters in
> the wrong order. They also haven't been tested (I have tested the basic
> formula in Excel though), so may need tweaking. (
>
> Helpful Harry
> Approaching four weeks of hopeless Internet Provider problems. xo(
 >> Stay informed about: How to search for a substring? 

Out of curiousity, why are you glad you dumped FoxPro?

> I'm fairly new at Filemaker, but I'm glad I dumped FoxPro for it.
 >> Stay informed about: How to search for a substring?