Using REGEXP in searching
 

Anyone having experience in MySQL and REGEXP ?

I have 1 input field, "what"

Now, the value from the "what" field can contain several substrings and I like each of them to be searched. Can this be done by using REGEXP or do I need to split the "what" input string into arrays before searching ?. Or is there other tricks using the LIKE clause ?

Another issue while talking about REGEXP in MySQL. Let's say I have the input value "e-mail" and the database field contains the value "email". There must be some way to put some variants into the expression to accept the "-" and match the values.

Any examples is welcome.

what = Request.Form("what")





WHERE searchwords LIKE ' % " & what & " % ' "



WHERE searchwords REGEXP '[[:<:]]"&what&"'"

Re: Using REGEXP in searching
 

.... just to "simplyfy" my question a bit :-)


If the Input is ("e-mail") then I like to match db column value ("email")
And reverse:
If the Input is ("email") then I like to match db column value ("e-mail")

The string "e-mail" is just an example, it can can be any other words, and the hyphen can be anywhere in the word OR NOT.

I have busted my head off trying some acrobatic expressions for this operations, So any help or examples are extremely welcome.

Re: Using REGEXP in searching
 

The period is a wild card. You would first need to replace the string that you are working with. replace all "-" for "." and then setup your regex. This way it will match: email and e-mail. However, it will not match em-ail or -email. It has to be where it is, hence the wildcard.

Re: Using REGEXP in searching
 

Hi there :-)
Thank you for your time on this.

To handle spaces I also do replacements for that. This part is interesting
becourse it respond to both existing and NON existing spaces in database.

The hyphen "-" in the INPUT does only respond to existing hyphens in the database. Don't know if my thinking or coding is wrong, but the hyphen should be threathed like the space.

As long as the hyphen "-" exists in the INPUT string there is no fuzz.
----------------------------------------
Input:
e-mailmarketing OR e-mail marketing

Output:
e-mailmarketing
e-mail marketing
emailmarketing
email marketing


Now, without the hyphen "-"
----------------------------------------
Input
emailmarketing OR email marketing

Output:
emailmarketing
email marketing

whatinput = "e-mailmarketing"



If InStr(whatinput,"-") <> 0 Then

whatinput = Replace(whatinput,"-","[\-|\.]?")

what = whatinput



If InStr(what," ") <> 0 Then

what = Replace(what," ","[\ ]?")

End If



Else

what = whatinput

End If

Database query

REGEXP '[[:<:]]"&what&"'"

Re: Using REGEXP in searching
 

I must make an reverse on my previous post. It is obvious why not the hyphens been handled as the space. The space is replaced by space, if it exists.

The question remain, how to identify and match hyphens in database if no hyphens exists in the INPUT string.

Re: Using REGEXP in searching
 

you would have to remove them, or use some very complex regex which would be extremely slow. Something would be like:
[a-Z|-] for each character. You would end up having to search every character, and allow the possibility of it being a hyphen. My suggestion.. forget about it or remove hypens from words, or replace words that are common with a common spelling:

email -> e-mail

It keeps consistency, and no one really cares.

Otherwise you are looking at 10 minute load times for records in excess of 1,000, if those strings are more than 200 characters long.

Re: Using REGEXP in searching
 

OK, guess you have a good point there.

I will give it a few more shots then I give up. :-)

I know you are extremely talented with database queries.
It would be great if you have a minute to help me analyse this question

I have 2 fields for input
1. what, anything that may identify a company, by name, words etc.
2. where , can contain strings as "statename", or "countyname" or "cityname". So this means, in my case searching for matches in 3 different tables looking for statename, county or city or specific addresses.

Within one single SQL query there is no problem searching for one at time.
I have tried using OR operations, but it doesn't works very well. And I try to limit the input fields to a minimum.

This example doesn't works:
AND KO.county LIKE '%"&where&"%' OR RG.state LIKE '%"&where&"%' "

I don't expect a full specification, but if you have a few hints how to start building on this handle different inputs and searching in different tables and keep the database connections to a minimum.

SQL = "SELECT CL.organisation, CA.adress,CA.postnr,CA.ort,RG.state,RG.stateID,KO.county,KO.countyID "&_

"FROM tbclients CL, tbclientaddress CA, tbstate RG, tbcounty KO "&_

"WHERE CL.clientID = CA.clientlink "&_

"AND CA.state = RG.stateID "&_

"AND CA.county = KO.countyID "&_

"AND CL.searchwords REGEXP '[[:<:]]"&what&"'" &_

"AND KO.county LIKE '%"&where&"%' "

Re: Using REGEXP in searching
 

You will only have one connection, but multiple record sets.

Change this a bit to do a more sophisticated join:

SQL = "SELECT CL.organisation, CA.adress,CA.postnr,CA.ort,RG.state,RG.stateID,KO.county,KO.countyID "&_

"FROM tbclientaddress CA JOIN tbclients CL ON CA.clientlink = CL.clientID JOIN tbstate RG ON CA.state = RG.stateID JOIN tbcounty KO ON CA.county = KO.countyID "&_

"WHERE CL.searchwords REGEXP '[[:<:]]"&what&"'" &_

"AND KO.county LIKE '%"&where&"%' "

Anyway, the reason why this won't work is because you are missing parenthesis:

AND (KO.county LIKE '%"&where&"%' OR RG.state LIKE '%"&where&"%') "

The reason for that it is failing at the or part, as it has no way to correctly organize the where segment. By adding the parenthesis, you are allowing it to be: (AND county=) or (And state=).

Re: Using REGEXP in searching
 

There is no medal big enough for you !

Many thanks !

I will send you a message if I come up with something useful in the Regexp issue.

Re: Using REGEXP in searching
 

Well for the regex, what you can do is do the search for the hyphenless version. Then when inside the search, do a regex replace command to remove all hyphens. That way it will match regardless