•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP section within the Web Development category of DaniWeb, a massive community of 374,634 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,431 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our ASP advertiser: Lunarpages ASP Web Hosting
Views: 2046 | Replies: 45 | Solved
 |
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation: 
Rep Power: 3
Solved Threads: 61
yeah, but no one will hire me.
it's crappy.
it's crappy.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
Well, I don't really believe that :-)
I have a biz idea if you are interested ?. Not sure if it work, but if it does ..?
Give me a couple of days to phrase it in proper english and get back to you.
I have a biz idea if you are interested ?. Not sure if it work, but if it does ..?
Give me a couple of days to phrase it in proper english and get back to you.
Last edited by TobbeK : Mar 4th, 2008 at 6:46 pm.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
I know I have put "solved" to this thread but I put my hope to some answer anyway.
I have a new challenge in my search facility.
The input field "where" are planned to be used for different searches as state, county, city or addresses.
Now, the address format used up here in the Northpole are as followed:
streetname [space] streetnumber [space] zipcode [space] city
Like this:
Coldstreet 123 12345 Freezytown
So if the input are:
where = "Coldstreet 123 12345 Freezytown"
I like to replace "Freezytown" with "" becourse I can identify unique records anyway by Coldstreet 123 and zipcode 12345, which have to be splitted for search in database.
If the input are:
where = "Coldstreet 123 12345"
Leave it as it is but it must be splitted for search in database. The Coldstreet 123 alone is not unique, but together with zipcode 12345 it is.
If the input are:
where = "Coldstreet 123"
Or
where = "12345"
Or
where = "Freezytown"
Leave it just as it is
Example code of the splitfunction so far..
Output:
0 = Coldstreet
1 = 123
2 = 12345
3 = Freezytown
The db query so far...
I have a new challenge in my search facility.
The input field "where" are planned to be used for different searches as state, county, city or addresses.
Now, the address format used up here in the Northpole are as followed:
streetname [space] streetnumber [space] zipcode [space] city
Like this:
Coldstreet 123 12345 Freezytown
So if the input are:
where = "Coldstreet 123 12345 Freezytown"
I like to replace "Freezytown" with "" becourse I can identify unique records anyway by Coldstreet 123 and zipcode 12345, which have to be splitted for search in database.
If the input are:
where = "Coldstreet 123 12345"
Leave it as it is but it must be splitted for search in database. The Coldstreet 123 alone is not unique, but together with zipcode 12345 it is.
If the input are:
where = "Coldstreet 123"
Or
where = "12345"
Or
where = "Freezytown"
Leave it just as it is
Example code of the splitfunction so far..
where = "Coldstreet 123 12345 Freezytown" Words = Split(where, " ") For i = 0 to Ubound(Words) Response.Write i & " = " & Words(i) & "<br>" Next
Output:
0 = Coldstreet
1 = 123
2 = 12345
3 = Freezytown
The db query so far...
SQL = "SELECT CL.organisation, CA.adress,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&"'" &_ "AND (REPLACE(CA.zipcode,' ','') REGEXP '[[:<:]]"&where&"' "&_ "OR REPLACE(KO.county,'-','') REGEXP '[[:<:]]"&where&"' "&_ "OR REPLACE(RG.state,'-','') REGEXP '[[:<:]]"&where&"') "
Last edited by TobbeK : Mar 5th, 2008 at 12:28 pm.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
The splitting function so far...
I am not yet satisfied.
It works quiet well if the city doesn't contain any space:
Also I must probably set some limitations to this while if a string holds a streetname with a missing streetnumber must at least be followed by a zipcode, otherwise it would be threathed as a cityname.
And a cityname must have a zipcode before or nothing.
I am not yet satisfied.
It works quiet well if the city doesn't contain any space:
Also I must probably set some limitations to this while if a string holds a streetname with a missing streetnumber must at least be followed by a zipcode, otherwise it would be threathed as a cityname.
And a cityname must have a zipcode before or nothing.
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 = 2 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
Last edited by TobbeK : Mar 5th, 2008 at 3:09 pm.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
The final version (maybe)
As long as there is no spaces in streetname or cityname it seems to work pretty much as I wanted.
If anyone comes up with some ideas how deal with spaces it would be welcome.
As long as there is no spaces in streetname or cityname it seems to work pretty much as I wanted.
If anyone comes up with some ideas how deal with spaces it would be welcome.
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
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
With city, do a split at the space, and then do the following:
For numero = 0 To UBounds(array)
sql &= " OR city LIKE '%" & array(numero) & "%'
If numero > 0 Then sql &= " OR city LIKE '%" & array(numero) & "%" & array(numero-1) & "%'"
Next
For numero = 0 To UBounds(array)
sql &= " OR city LIKE '%" & array(numero) & "%'
If numero > 0 Then sql &= " OR city LIKE '%" & array(numero) & "%" & array(numero-1) & "%'"
Next
Last edited by SheSaidImaPregy : Mar 6th, 2008 at 8:54 am.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
Thanks
I know your examples ALWAYS working
To be absolute honest, I dont don't really know where to put it =-(
I must have seen to many lines of code the past days. Sorry for hanging to much weight on your time and effort.
I know your examples ALWAYS working
To be absolute honest, I dont don't really know where to put it =-(
I must have seen to many lines of code the past days. Sorry for hanging to much weight on your time and effort.
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
Where = "Coldstreet 123A 12345 Freezytown"
Okay, now obviously this is:
123 Coldstreet (apt. A??)
Freezytown, (State?), 12345
Right?
Okay, now obviously this is:
123 Coldstreet (apt. A??)
Freezytown, (State?), 12345
Right?
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
The letters like "A" is used sometimes in streetnumbers and in those cases it is a part of the number, like the 123A or 123 A.
The format is:
streetaddress, streetnumber, zipcode, city
Some actually use the apt in addresses but it's rare, and it then looks like this:
streetaddress, streetnumber aptnumber, zipcode, city
The database holds the streetname and streetnumber together in the same column.
I guess the US format is as you write with the streetnumber first, followed by streetname.
The format is:
streetaddress, streetnumber, zipcode, city
Some actually use the apt in addresses but it's rare, and it then looks like this:
streetaddress, streetnumber aptnumber, zipcode, city
The database holds the streetname and streetnumber together in the same column.
I guess the US format is as you write with the streetnumber first, followed by streetname.
Last edited by TobbeK : Mar 6th, 2008 at 11:12 am.
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
Yes it is. Okay then this is how I would do it:
'Prebuilt SQL query is set to sql up to WHERE clause
Dim array()
array = words.split(" ")
For numero = 0 To UBounds(array)
If numero = 0 Then sql = sql & " street LIKE '%" & array(numero) & "%'
If numero = 1 Then sql = sql & " OR streetnumber LIKE '%" & array(numero) & "'"
If numero = 2 Then sql = sql & " OR zipcode LIKE '%" & array(numero) & "%'"
If numero = 3 Then sql = sql & " OR city LIKE '%" & array(numero) & "%'"
If numero > 3 Then sql = sql & " OR city LIKE '%" & array(numero) & "%" & array(numero-1) & "%' OR city LIKE '%" array(numero) & "%'"
Next
execute(sql) Last edited by SheSaidImaPregy : Mar 6th, 2008 at 11:15 am.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb ASP Marketplace
Linear Mode
Similar Threads
- Replace a space with an underscore SOLVED (PHP)
- Limiting grep searches to a given file type? (Web Developers' Lounge)
- Desiging a set of rules for a match (C++)
Other Threads in the ASP Forum
- Previous Thread: creating download object.
- Next Thread: Updating table in a remote database
