need "exact match within string" in ASP

and then count the number of times it is present

[Woz]

I know it is a little early for Scripting week but I cannot wait. This one has me stumped.

I am using ASP to query Access 97 db on IIS5

FIRST:-

I need to find exact matches for search terms within a string. EG "cat" will find cat but not catastrophe from within a large string.

I cannot use "=" as that would match the whole string and I only want to find the exact match within the string.

I am trying "like '%[!a-z][search term][!a-z]%'" which is pretty clumsy and also doesn't pick up the search term if it is at the beginning of a sentence.

Can anyone help me with the syntax here? I have racked my brains coming up with all sorts of convoluted solutions that all end up having a flaw. I have been looking around ASP fora and it seems a few people are asking the same question but getting no answers. (you might see my question in some of them...) I am sure there must be some operator within SQL as there is in FMP but I cannot find it.

SECOND:-

I want to count the number of occurences of a search term within a string. InStr only finds the first occurence and returns the character position. That is fine for finding out if the search term is present but not how many times. I want something similar to PatternCount in FMP. Any Ideas?

Thanks all

Onya
Woz

[Xoc]

I just read your question more carefully, and realized this isn't an ASP question at all; it's an SQL question. ASP is merely misdirection. I suggest posting on an Access forum, not a scripting forum. Try posting here: news://news.microsoft.com/microsoft.public.access.queries

[Woz]

>it's an SQL question.

Yes I am aware of that, but as SQL is often an integral part of scripting in it's many forms I thought someone here may have an idea. Hope so 'cos I am pullilng the hair out on this one.

Thanks anyway Xoc

Onya
Woz

[sugarkane]

Woz, this depends muchly on the implementation of regular expressions in your database, and I'm not too sure how Access does it...

Using MySql, your query would be something like:

SELECT foo FROM bar WHERE foo REGEXP " search_term( ¦[[:punct:]])"

This would match occurences of search_term that were preceded by a space, and followed by a space or a punctuation character.

It might be worth trying something along these lines, let us know how you get on...

[Woz]

>preceded by a space, and followed by a space or a punctuation character.

Close but no cigar! That is almost what I am trying now but that precludes the first word in the sentence because there is no preceeding space.

[sugarkane]

>there is no preceeding space.

Okay, try this:

SELECT foo FROM bar WHERE foo REGEXP "(^¦ )search_term( ¦[[:punct:]])"

That will match if search_term is either preceded by a space or at the start of the line. Does that work?

[Woz]

probably, but I dunno how to do that in ASP/Access. But it give me some thought. Thanks.

[Gorufu]

G'day Woz

You might be able to get a few ideas from [url=www.hotscripts.com/ASP/Scripts_and_Components/Database_Tools/]HotScripts[/url] in the ASP Database Tools section.

[rpking]

Use this query...

SELECT * FROM table WHERE column LIKE '%term%'

The LIKE query will find all occurences of term with any number of characters either before or after ('%' sign). To find instances of only one extra character, use the '_' wildcard.

(These wilcards work in ASP with Access - with SQL Server, they are different, * and ? I think...)

[Woz]

Thanks RP but, although I use that syntax all the time, it is not appropriate for this one.

For example, "%at%" would find at, attack, cat, catastrophy, etc. I want to find exactly "at" and no more. I can't use "=at" because that look to match the whole field. What I want to do is find an exact match on a substring within a string. Follow? Hard one to explain.

Onya
Woz

[Fusioneer]

This is just off the top of my head but I coded a search app some time ago and I padded the search term when I needed to find a discrete substring within a larger string.

If you need to find "ill" but not "illness" or "killer" you have to search for " ill ", eg. include the whitespace around the string.

The other way is to treat the string as a space-delimited list and count matching elements in the list. Cold Fusion syntax:

<cfset count = ListValueCount(string, substring, " ")>

That's the quick and dirty, for proper string matching you should also strip all other characters from the string like full stops, comma's, etc. otherwise they will be treated as part of each substring.