Search for multiple occurences of a string (with wildcards) in another longer string

[Hank Scorpio]

I'm returning to FMP after a couple of years and am having real trouble getting back up to speed.

I have a table of DNA sequence data (just simple text strings 700+ chars long, no spaces) and another table with shorter sequences (<50 bases). I want to know the number of times the shorter sequences are present in the long ones.

Patterncount works really easily most of the time but some of the search strings contain wildcards so I need to use Find but can't work out how to loop the search all the way through and sum the answer.

Thanks.

[comment]

How many wild cards can a sequence have at most? Does a wild card signify the length or does it mean "any number of unknown characters (including zero)"?

[Hank Scorpio]

The lengths are going to vary but generally the wildcards are going to be within the strings

Such as CCCNCNNNCT or CACCTGNNNNTTCCC where N can be A, C, T or G.

Parsing the strings so that N is automatically changed to @ I can handle.

If Patterncount could handle wildcards then it would be easy.

[comment]

If Patterncount could handle wildcards

If wishes were horses, beggars would ride... This is quite an interesting puzzle. I would suggest you use a recursive custom function here. The basic idea is to break the search string into:

searchstring1, wildcard/s, searchstring2, wildcard/s, searchstring3 ...

Start by finding the position of searchstring1 in text. If not found, you're done. If found, calculate where the text is supposed to pick up the sequence (i.e. after searchString1 and the length of the following wildcard/s string) and pass that to the function, prefixed by, say "@". Also pass the rest of the search string as:

"@" & searchstring2, wildcard/s, searchstring3 ...

---

Note: this will find if the entire search string appears in text or not. To count how many times, you'd need another custom function to repeat the process with the rest of the text (i.e. the portion of the text starting where the previous occurrence of search string ended).

Edited November 22, 2010 by Guest

[Hank Scorpio]

Ok thanks for that, I was hoping that I was missing an easy way of doing it but clearly I'm not.

Given that this is something I'm just doing for a publication, I think I'll just limit the searches to exact matches, which is the bulk of them anyway.

[comment]

I was hoping that I was missing an easy way

Alas, no. It is very easy to count a pattern that does NOT contain wild cards; it is also very easy to do a find with wild cards to see which records contain the string at least once. But counting a pattern with wild cards is far from trivial.

[dansmith65]

You should be able to do this with Regular Expressions; which don't exist in FileMaker. However, you can use a plug-in that supports them. Do a search on it, and you will find more than a few options: http://www.google.com/search?q=filemaker+regular+expression