ASP RSS ASP RSS

Using REGEXP in searching

Thread Solved
1 2 Last »

•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Using REGEXP in searching

 
0
  #1
Feb 29th, 2008
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.


ASP Syntax (Toggle Plain Text) 
  1.  
  2. what = Request.Form("what")
  3.  
  4.  
  5. WHERE searchwords LIKE ' % " & what & " % ' "
  6.  
  7. WHERE searchwords REGEXP '[[:<:]]"&what&"'"
Last edited by TobbeK; Feb 29th, 2008 at 2:07 pm.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Using REGEXP in searching

 
0
  #2
Mar 3rd, 2008
.... 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Using REGEXP in searching

 
0
  #3
Mar 3rd, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Using REGEXP in searching

 
0
  #4
Mar 4th, 2008
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


ASP Syntax (Toggle Plain Text) 
  1. whatinput = "e-mailmarketing"
  2.  
  3. If InStr(whatinput,"-") <> 0 Then
  4. whatinput = Replace(whatinput,"-","[\-|\.]?")
  5. what = whatinput
  6.  
  7. If InStr(what," ") <> 0 Then
  8. what = Replace(what," ","[\ ]?")
  9. End If
  10.  
  11. Else
  12. what = whatinput
  13. End If

Database query

ASP Syntax (Toggle Plain Text) 
  1. REGEXP '[[:<:]]"&what&"'"
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Using REGEXP in searching

 
0
  #5
Mar 4th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Using REGEXP in searching

 
0
  #6
Mar 4th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Using REGEXP in searching

 
0
  #7
Mar 4th, 2008
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.



ASP Syntax (Toggle Plain Text) 
  1. SQL = "SELECT CL.organisation, CA.adress,CA.postnr,CA.ort,RG.state,RG.stateID,KO.county,KO.countyID "&_
  2. "FROM tbclients CL, tbclientaddress CA, tbstate RG, tbcounty KO "&_
  3. "WHERE CL.clientID = CA.clientlink "&_
  4. "AND CA.state = RG.stateID "&_
  5. "AND CA.county = KO.countyID "&_
  6. "AND CL.searchwords REGEXP '[[:<:]]"&what&"'" &_
  7. "AND KO.county LIKE '%"&where&"%' "
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Using REGEXP in searching

 
0
  #8
Mar 4th, 2008
You will only have one connection, but multiple record sets.

Change this a bit to do a more sophisticated join:
ASP Syntax (Toggle Plain Text) 
  1. SQL = "SELECT CL.organisation, CA.adress,CA.postnr,CA.ort,RG.state,RG.stateID,KO.county,KO.countyID "&_
  2. "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 "&_
  3. "WHERE CL.searchwords REGEXP '[[:<:]]"&what&"'" &_
  4. "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=).
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Using REGEXP in searching

 
0
  #9
Mar 4th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Using REGEXP in searching

 
0
  #10
Mar 4th, 2008
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
Reply With Quote Quick reply to this message  
Reply
1 2 Last »

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ASP Forum
Thread Tools Search this Thread



archive asp asp.net aspandmssqlserver2005 aspandmssqlserver2005connection aspconnection connection database databaseconnection diagnostics dreamweaver excel fso iis microsoft msmsql mssql2005 mssqlserver2005 mssqlserver2005andasp mssqlserverandasp opentextfile record searchbox selectoption server single specfic sqlserver sqlserverconnection toolkit webserver windows7
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC