Using REGEXP in searching - Page 2

Question

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

TobbeK 0 Junior Poster · Answer 1 · 2008-03-07T00:02:48+00:00

What am I missing ....
I feel like a complete doodle .....

You are allowed to laugh and slap your knees :-)

whatinput = "Coldstreet 123A 12345 Freezytown"


array = whatinput.split(" ")


SQL = "SELECT CL.organisation, CA.street,CA.zipcode,CA.city,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 " For numero = 0 To UBounds(array)
If numero = 0 Then SQL = SQL & " CA.street LIKE '%" & array(numero) & "%' "&_
If numero = 1 Then SQL = SQL & " OR CA.streetnumber LIKE '%" & array(numero) & "'" &_
If numero = 2 Then SQL = SQL & " OR CA.zipcode LIKE '%" & array(numero) & "%'" &_
If numero = 3 Then SQL = SQL & " OR CA.city LIKE '%" & array(numero) & "%'" &_
If numero > 3 Then SQL = SQL & " OR CA.city LIKE '%" & array(numero) & "%" & array(numero-1) & "%' OR CA.city LIKE '%" array(numero) & "%'"
Next
execute(sql)
Set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn


Do While Not RS.EOF 

etc.. etc..

I tried this also without the execute(sql).

TobbeK 0 Junior Poster · Answer 2 · 2008-03-07T01:14:22+00:00

Ahh hold on a sec.

The index must be dynamic, I did not see that before.

I'll throw up some code later on...

SheSaidImaPregy 28 Veteran Poster · Answer 3 · 2008-03-07T01:27:24+00:00

whatinput = "Coldstreet 123A 12345 Freezytown"


array = whatinput.split(" ")


SQL = "SELECT CL.organisation, CA.street,CA.zipcode,CA.city,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 "

For numero = 0 To UBounds(array)
  Select Case numero
    Case 0
      SQL = SQL & " CA.street LIKE '%" & array(numero) & "%' "
    Case 1
      SQL = SQL & " OR CA.streetnumber LIKE '%" & array(numero) & "%'"
    Case 2
      SQL = SQL & " OR CA.zipcode LIKE '%" & array(numero) & "%'"
    Case 3
      SQL = SQL & " OR CA.city LIKE '%" & array(numero) & "%'"
    Case > 3
      SQL = SQL & " OR CA.city LIKE '%" & array(numero - 1) & "%" & array(numero) & "%' OR CA.city LIKE '%" array(numero) & "%'"
  End Select
Next

Set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn


Do While Not RS.EOF 

etc.. etc..

This is your SQL string for the following line:

"Coldstreet 123A 12345 Freezy Town"

SQL = ....
For ....
....
Next
....

'SQL Output String:
SQL = "SELECT CL.organisation, CA.street,CA.zipcode,CA.city,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 CA.street LIKE '%Coldstreet%'"&_
" OR CA.streetnumber LIKE '%123A%'" &_
" OR CA.zipcode LIKE '%12345%'" &_
" OR CA.city LIKE '%Freezy%'" &_
" OR CA.city LIKE '%Freezy%Town%'"
" OR CA.city LIKE '%Town%'"

** Some changes have been made to my original post. Please copy this one.

SheSaidImaPregy 28 Veteran Poster · Answer 4 · 2008-03-07T01:31:15+00:00

But please answer me this... so what happens if my address is..

15011 Via Hondonado apt. E
San Diego, CA 92129

How would you handle that, and how would you split the strings?

My suggestion, place a comma or something that is unique to separate portions. Then separate them.

Via Hondonado apt. B;15046;92129;San Diego

Then do a split for ";", and replace all " " with "%".

Then you will only ever have up to 4 strings.
Street Name, Street Number, Zip Code, City.

And this way you are set incase someone has a bunch of spaces in their street name.

TobbeK 0 Junior Poster · Answer 5 · 2008-03-07T01:37:23+00:00

Ahh hold on a sec.

The index must be dynamic, I didn't see that before.

The user input can be anything like this:

Whereinput = "Coldstreet" ' streetname
Or
Whereinput = "Coldstreet 123A" ' streetname and streetnumber
Or
Whereinput = "Coldstreet 123A 12345" ' streetname and streetnumber and zipcode
Or
Whereinput = "Coldstreet 12345" ' streetname and zipcode
Or
Whereinput = "12345 Freezytown" ' zipcode and city
Or
Whereinput = "12345" ' zipcode
Or
Whereinput = "Freezytown" ' city

I "just" have to decide which combinations that are not allowed like the ones below.

Non valid search strings:

Whereinput = "123A" ' streetnumber alone
Or
Whereinput = "123A 12345" ' streetnumber and zipcode
Or
Whereinput = "123A Freezytown" ' streetnumber and city

I'll throw up some more code later on for review...

My current code actually handle different combinations as I described, but Not as long there are any spaces in streetname and/or cityname like this.

Cold Street
or
Freezy Town

Where = "Coldstreet 123A 12345 Freezytown"

Words = Split(Where, " ")

For i = 0 to Ubound(Words)

If i = 0 AND i < UBound(Words) AND IsNumeric(Words(i)) = False Then
 Response.Write i & " = Street: " & Words(i) & "<br>"
End If

If i = 1 AND i < UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then
Response.Write i & " = Streetnumber: " & Words(i) & "<br>"
 ElseIf i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then
Response.Write i & " = Streetnumber: " & Words(i) & "<br>"
End If

If i > 0 AND i < UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then
 Response.Write i & " = Zipcode: " & Words(i) & "<br>"
 ElseIf i = UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then
 Response.Write i & " = Zipcode: " & Words(i) & "<br>"
End If

If i > 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then
 Response.Write i & " = City: " & Words(i) & "<br>"
ElseIf i = 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then
 Response.Write i & " = City: " & Words(i) & "<br>"
End If

Next

SheSaidImaPregy 28 Veteran Poster · Answer 6 · 2008-03-07T02:01:28+00:00

Yes but how do you differentiate between the inputs?

Do you have checkboxes or something?

TobbeK 0 Junior Poster · Answer 7 · 2008-03-07T02:16:17+00:00

At this point just by the string.

As I have thinked yet, and coded a bit:
streetnumber is normally no longer than 4 chars, containing a start with one or more digits and sometimes with a ending letter. It is very rare this is longer than 3-4 chars.

zipcode can only be 5 digits in length.

Now, we can by these parameters separate atleast two possible inputs. Then we have the streetname and cityname that is a bit tricky atleast when it comes to spaces.

If the input holds just a streetname or a cityname they can be used just as is for db search, and probably even when the input holds both, a split then db search.

Test the code I have provided and cut off different pieces from the input, it is actually works.

Last update:

Where = "Coldstreet 123A 12345 Freezytown"
Where = Trim(Where)

Words = Split(Where, " ")

For i = 0 to Ubound(Words)

If i = 0 AND i < UBound(Words) AND IsNumeric(Words(i)) = False Then
 Response.Write i & " = Street: " & Words(i) & "<br>"
End If

If i = 1 AND i < UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then
 Response.Write i & " = Streetnumber: " & Words(i) & "<br>"
  ElseIf i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = True AND Len(Words(i)) < 5 Then
 Response.Write i & " = Streetnumber: " & Words(i) & "<br>"
End If

If  i < UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then
 Response.Write i & " = Zipcode: " & Words(i) & "<br>"
  ElseIf i = UBound(Words) AND (IsNumeric(Words(i)) = True) AND Len(Words(i)) >= 5 Then
 Response.Write i & " = Zipcode: " & Words(i) & "<br>"
End If

If i > 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then
 Response.Write i & " = City: " & Words(i) & "<br>"
  ElseIf i = 0 AND i = UBound(Words) AND IsNumeric(Left(Words(i),1)) = False Then
 Response.Write i & " = City: " & Words(i) & "<br>"
End If

Next

SheSaidImaPregy 28 Veteran Poster · Answer 8 · 2008-03-07T03:46:21+00:00

Yes I am sure it works, but you shouldn't rely on users being narrow minded about how to enter in their address.

You should have an identifier that separates all of them, IMO

TobbeK 0 Junior Poster · Answer 9 · 2008-03-07T04:08:33+00:00

I am counting on the possibility with misspelling and even some strange hits. It cannot be absolute failsafe.

This is suppose to serve as a search engine for company quick search.

A visiting user can use two fields for that

Example:
Input fields:
what = "programming"
where = "12345 freezytown"

Output:
1 hit:
SheSaidImaPregy & Co
ASP Professional and posting shark
ColdStreet 123A
12345 Freezytown

"didn't know that guy works in Freezy', Cool !"

But I must try to design this facilty to be as exact as possible.
And in addition I am going to create a form for more advanced searches for users needed exact hits an so on.

Separators can be good to have as you suggest.

SheSaidImaPregy 28 Veteran Poster · Answer 10 · 2008-03-07T21:00:44+00:00

Are you stuck with doing this in ASP?

Asp.net would be much better for this.

TobbeK 0 Junior Poster · Answer 11 · 2008-03-07T21:33:40+00:00

Sorry to say this, yes

Have no experience at all in ASP.NET. I have this in planning schedule but it doesn't help much for now :-). Using traditional vbscript coding in ASP.NET maybe isn't the best for performance ?

I have some new code that I going to throw up here soon with separators and comments. It might be the last try on this. If you have a minute later to throw an professional eyeball on that would be more than welcome.

/
And I will send you a message later as I promised earlier about an idea in business. Your have probably lot's of work going on already but I'll take my chances.

SheSaidImaPregy 28 Veteran Poster · Answer 12 · 2008-03-07T21:41:27+00:00

Actually vbscript/vb.net works the same in asp.net.

It's not much of a conversion, and is much easier to do in vb.net than regular vbscript.

However, it would take time to learn the controls of asp.net, but it is definitely worth it and will save you more coding than you think ^^

Sure, shoot with whatever you got.

TobbeK 0 Junior Poster · Answer 13 · 2008-03-07T23:26:34+00:00

The final try on this, before find some other approach to it.

I works sometimes, and orther time some results are strange when spaces are present or not.

What I have try to achive here is to concatenate the streetadress with streetnumber. It doesn't work very well.

The purpose of the possibility to do searches like this is to actually return what the user ask for, a more detailed question will return more exact hits

Example:
A string like this is containing very exact information for db search.
whereinput = "Coldstreet, 123A, 12345, Freezytown"
A string like this is just as exact for db search.
whereinput = "Coldstreet, 123A,12345"

Less exact but enough for db search
whereinput = "Coldstreet, 123A"
Or
whereinput = "12345"
Or
whereinput = "12345, Freezytown"
Or
whereinput = "Freezytown"

Also
I have created a variable (hybrid) for different inputs which can be either city, county or state

whereinput = "Coldstreet, 123A, 12345, Freezytown"
whereinput = Trim(whereinput)

where = Split(whereinput, ",")


For i = 0 to Ubound(where)

If i = 0 AND i < UBound(where) AND IsNumeric(where(i)) = False Then
  	Session("where") = where(i)
	adress = session("where")
ElseIf adress <> "" AND i = 1 AND i < UBound(where) AND IsNumeric(Left(where(i),1)) = True AND Len(where(i)) < 5 Then
 	Session("where") = where(i)
 	gatunr = session("where")
	adress = adress &" "& streetnumber
 Response.Write i & " = Adress: " & adress & "<br>"

ElseIf adress = "" AND i < UBound(where) AND IsNumeric(Left(where(i),1)) = True AND Len(where(i)) < 5 Then
  	Session("where") = where(i)
	streetnumber = session("where")
	adress = streetnumber
 Response.Write i & " = Adress: " & adress & "<br>"
End If

If i < UBound(where) AND (IsNumeric(where(i)) = True) AND Len(where(i)) >= 5 Then
 Response.Write i & " = Postnr: " & where(i) & "<br>"
	Session("where") = where(i)
	zipcode = session("where")
 ElseIf i = UBound(where) AND (IsNumeric(where(i)) = True) AND Len(where(i)) >= 5 Then
 Response.Write i & " = Postnr: " & where(i) & "<br>"
	Session("where") = where(i)
	zipcode = session("where")
End If

If i > 0 AND i = UBound(where) AND IsNumeric(Left(where(i),1)) = False Then
 Response.Write i & " = City: " & where(i) & "<br>"
  	Session("where") = where(i)
	hybrid = session("where")
 ElseIf i = 0 AND i = UBound(where) AND IsNumeric(Left(where(i),1)) = False Then
 Response.Write i & " = Address/City/County/State: " & where(i) & "<br>"
  	Session("where") = where(i)
	hybrid = session("where")
End If

Next



If InStr(zipcode," ") <> 0 Then
zipcode = Replace(zipcode," ","[\ ]?")
End If



SQL = "SELECT CL.organisation, CA.address,CA.zipcode,CA.city,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 (REPLACE(CL.searchwords,'-','') REGEXP '[[:<:]]"&what&"'" &_
"OR REPLACE(CA.phone,'-','') REGEXP '[[:<:]]"&what&"'" &_
"OR REPLACE(CL.organisation,'-','') REGEXP '[[:<:]]"&what&"')" &_
"AND (REPLACE(CA.address,' ','') REGEXP '[[:<:]]"&address&"' "&_
"AND REPLACE(CA.zipcode,' ','') REGEXP '[[:<:]]"&zipcode&"' )" &_
"AND (REPLACE(CA.city,'-','') REGEXP '[[:<:]]"&hybrid&"' " &_
"OR REPLACE(KO.county,'-','') REGEXP '[[:<:]]"&hybrid&"' " &_
"OR REPLACE(RG.state,'-','') REGEXP '[[:<:]]"&hybrid&"') "
Set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn


Do While Not RS.EOF

etc ... etc ...

TobbeK 0 Junior Poster · Answer 14 · 2008-03-10T04:00:24+00:00

The final .... (probably)

Now it is actually does what it should.

Any input combination is handled as it is supposed to.

Even an an input like this is separated in the manner it should.
whereinput = "Cold street 123 A 123 45"

Output:
Cold street 123 A
123 45

I have played a bit with the index and the values stays at the same index positions but are placed into different variables.

Everything seems to work. I still have one piece left, and that is the cityname that can appear at the very end of the string or by it's own. I havn't figured that out yet, and hopefully I get some help with that.

The streetname alone or a cityname alone should populate a variable named hybrid.
If there is any spaces in that string the first substring populate streetname second substring the hybrid variable.

whereinput = "Cold street 123 A 123 45"
whereinput = Trim(whereinput)

where = Split(whereinput, " ")

For i = 0 To UBound(where)


If i = 0 AND i = Ubound(where) AND IsNumeric(where) = False Then

	hybrid = where(i)

	ElseIf i = 0 AND i < Ubound(where) AND IsNumeric(where) = False Then
	streetname = where(i)

	ElseIf i = 1 AND i < Ubound(where) AND IsNumeric(where) = False Then
	streetname = where(i)

	ElseIf i = 2 AND i < Ubound(where) AND IsNumeric(where) = False Then
	streetname = where(i)

	ElseIf i = 3 AND i < Ubound(where) AND IsNumeric(where) = False Then
	streetname = where(i)

	ElseIf i = 4 AND i < Ubound(where) AND IsNumeric(where) = False Then
	streetname = where(i)

End If


If i = Ubound(where) AND IsNumeric(Left(where(i),1)) = True AND Len(where(i)) < 5  Then

	If i = 1 Then
	streetname = streetname&" "&where(i)
	End If

	If i = 2 Then
	streetname = where(i-2)&" "&streetname&" "&where(i)
	End If

	If i = 3 Then
	streetname = where(i-3)&" "&where(i-2)
	End If

	If i = 4 Then
	streetname = where(i-4)&" "&where(i-3) & " " &where(i-2)
	End If

	If i = 5 Then
	streetname = where(i-5)&" "&where(i-4) & " " &where(i-3) & " " & where(i-2)
	End If

	If i = 6 Then
	streetname = where(i-6)&" "&where(i-5) & " " &where(i-4) & " " & where(i-3)
	End If

	Response.Write(i &" = " & streetname  & "<br>")

End If


If i = Ubound(where) AND IsNumeric(Left(where(i),1)) = False  Then

	If i = 6 Then
	streetname = where(i-6)&" "&where(i-5) & " " &where(i-4) & " " & where(i-3)
	End If

	If i = 5 Then
	streetname = where(i-5)&" "&where(i-4) & " " &where(i-3) & " " & where(i-2)
	End If

	If i = 4 Then
	streetname = where(i-4)&" "&where(i-3) & " " & where(i-2)
	End If

	If i = 3 Then
	streetname = where(i-3)&" "&where(i-2)
	End If

	If i = 2 Then
	streetname = streetname & " " &where(i)
	End If

	If i = 1 Then
	streetname = streetname & " " &where(i)
	End If

	Response.Write(i &" = " & streetname  & "<br>")
	Response.Write(i &" = " & hybrid  & "<br>")

End If


If i = Ubound(where) AND IsNumeric(Left(where(i),1)) = True AND Len(where(i)) = 5  Then

	If i = 0 Then
	zipcode = where(i)
	End If

	If i = 1 Then
	streetname = where(i-1)
	zipcode = where(i)
	End If

	If i = 2 Then
	streetname = where(i-2) &" "& where(i-1)
	zipcode = where(i)
	End If

	If i = 3 Then
	streetname = where(i-3) &" "& where(i-2) &" "& where(i-1)
	zipcode = where(i)
	End If

	If i = 4 Then
	streetname = where(i-4) &" "& where(i-3) &" "& where(i-2) &" "& where(i-1)
	zipcode = where(i)
	End If

	If i = 5 Then
	streetname = where(i-5) &" "& where(i-4) &" "& where(i-2) &" "& where(i-1)
	zipcode = where(i)
	End If

	Response.Write(i &" = " & streetname  & "<br>")
	Response.Write(i &" = " & zipcode  & "<br>")

ElseIf i = Ubound(where) AND IsNumeric(Left(where(i),2)) = True AND Len(where(i)) = 2  Then

	If i = 4 Then
	streetname = where(i-4) &" "& where(i-3) &" "& where(i-2)
	zipcode = where(i)
	End If

	If i = 3 Then
	streetname = where(i-3) &" "& where(i-2)
	zipcode = where(i-1)&where(i)
	End If

If IsNumeric(Left(where(i-1),3)) = True AND Len(where(i-1)) = 3 Then

	If i = 2 Then
	streetname = where(i-2) '&" "& where(i-1)
	zipcode = where(i-1)&where(i)
	End If

End If

zipcode = where(i-1) & where(i)
Response.Write(i &" = " & zipcode  & "<br>")

End If


Next


Response.Write("<br>")
Response.Write("streetname: " & streetname) & "<br>"
Response.Write("zipcode: " & zipcode)  & "<br>"
Response.Write("hybrid: " & hybrid)

SheSaidImaPregy 28 Veteran Poster · Answer 15 · 2008-03-11T19:41:17+00:00

Instead of all these if's, else if's, etc. Use select case statements. It's much faster and the server doesn't have to read every if statement, just till the one it reaches.

Will improve performance a lot.

TobbeK 0 Junior Poster · Answer 16 · 2008-03-12T02:53:41+00:00

Yes you are right. I have thought about that and I will change it to Selects in the next version that is almost finished and almost completely rewrited. Also the Select Case looks a lot better :-)