FP and Access string and keyword search


M

Mettá

Can anyone tell me how to cut down a string search by single words to search
a db memo field.

So that if a user types in "one two three" the search will first look for
the whole string and if nil cut it down by one word and then search again
and then by two etc...

I know
Select * from DBname where field1 LIKE '::field1::'

That's as far as my knowledge goes!

I know this might be complicated?

Thanks
M
 
Ad

Advertisements

S

Stefan B Rusynko

You would need to hand code the page to first parse the search string (say: one two three) into its individual words (based on the
spaces as separators), then run your query looking thru the words w/ OR conditions
- can get pretty server intensive depending on the length of the string entered by the user

--




| Can anyone tell me how to cut down a string search by single words to search
| a db memo field.
|
| So that if a user types in "one two three" the search will first look for
| the whole string and if nil cut it down by one word and then search again
| and then by two etc...
|
| I know
| Select * from DBname where field1 LIKE '::field1::'
|
| That's as far as my knowledge goes!
|
| I know this might be complicated?
|
| Thanks
| M
|
| --
| ---
|
|
 
M

MikeR

Here's a bit of VBScript. The page must be asp. The lines beginning with ' are comments.
The response.write lines will show what happens as the loop runs. They're for demo
purposes only.
Here's the MS word on VBScript:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vbscripttoc.asp

<%
str1 = "one two three"
p = 1
response.write "p = " & p & " str1 = " & str1 & " length = " & len(str1) & "<p>"
'Search for str1. If not found then do da loop.
do while p > 0
p = InStrRev(str1, " ")
str1 = Trim(left(str1, p))
'search for str1
response.write "p = " & p & " str1 = " & str1 & " length = " & len(str1) & "<p>"
loop
%>
MikeR 1st
 
M

Mettá

Thanks MikeR the First!

M

--
---
MikeR said:
Here's a bit of VBScript. The page must be asp. The lines beginning with '
are comments. The response.write lines will show what happens as the loop
runs. They're for demo purposes only.
Here's the MS word on VBScript:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vbscripttoc.asp

<%
str1 = "one two three"
p = 1
response.write "p = " & p & " str1 = " & str1 & " length = " & len(str1)
& "<p>"
'Search for str1. If not found then do da loop.
do while p > 0
p = InStrRev(str1, " ")
str1 = Trim(left(str1, p))
'search for str1
response.write "p = " & p & " str1 = " & str1 & " length = " & len(str1)
& "<p>"
loop
%>
MikeR 1st
 
Ad

Advertisements

S

Stefan B Rusynko

Good luck passing that to a SQL Select query w/o generating an error or w/o looping thru the DB multiple times
- and it doesn't parse out the individual words, it only cuts the string down by 1 word each time it loops (not the same thing)

Don't get me wrong
- I'm not being critical of your attempt to help

But Metta is asking for someone to provide a "code snippet" for a rather complex (but doable) series of code that must:
a) parse Any user input string (from 0 to X words - say up to 255 characters)
b) handle any punctuation in the string that would affect the SQL as in the user string below:
"Show me all about a one! Or show me two. What about three? And also one, two, three; plus one or two or three. How about three
two one?"
(the punctuation w/ just parsing spaces will cause the LIKE to not find any results)
c) handle any SQL key words (will the string allow include and/or processing?)
d) based on the parsing generate a single SQL Select for all words plus any words from 1 to X
e) and probably not include duplicates to avoid wasted DB loops
f) or since the search is using a LIKE avoid possible common words, say "a", to avoid meaningless results

IMHO
- that is a heck of a lot of "code snippet" to ask for (-;
--




| Here's a bit of VBScript. The page must be asp. The lines beginning with ' are comments.
| The response.write lines will show what happens as the loop runs. They're for demo
| purposes only.
| Here's the MS word on VBScript:
| http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vbscripttoc.asp
|
| <%
| str1 = "one two three"
| p = 1
| response.write "p = " & p & " str1 = " & str1 & " length = " & len(str1) & "<p>"
| 'Search for str1. If not found then do da loop.
| do while p > 0
| p = InStrRev(str1, " ")
| str1 = Trim(left(str1, p))
| 'search for str1
| response.write "p = " & p & " str1 = " & str1 & " length = " & len(str1) & "<p>"
| loop
| %>
| MikeR 1st
|
| Mettá wrote:
| > ...any chance of a code snippet??
| >
| > Thanks
| > M
| >
 
M

Mettá

Thank you Stefan for clarifying what it is I am after, it seems odd that no
one has a 'snippet' to offer beyond what Mike R has given me.

However, the code Mike R offered is now in use outside the actual sql as an
offering to users to reduce the number of words used, far from perfect, not
quite what I would like, but, it's a start!

The short term down side is it would be nice to include the last word (last
in string)
within the code as well as the reduced strings. and change the order (from
the left rather than the right) and....

Thank for the warning too, although it is not yet within the actual search
coding.

You can view it here
http://www.metta.org.uk/advance.asp?description=yoga+centre+in+London

M
 
M

MikeR

Hi Stefan -
I think if you re-read what Metta's original post requested, given the example there, my
snippet does exactly what was asked for. Pare a space delimited string down by a word for
as many words as there are.

It wasn't intended to be an enterprise grade solution. It was merely to stimulate thought,
and illustrate some of the basic VBScript usage. Why should the complexity of the solution
keep someone from answering a post?

MikeR 1st
 
S

Stefan B Rusynko

I agree his original wording implies removing a word at a time which is what you have done
But as you can see in his implementation test example he has posted, that it is cumbersome approach
- he is only removing the trailing word from a LIKE qry
(but the word sequence may be critical - it is possible only the middle word will get a result)
- and he hasn't been able to auto generate the SQL statement based on the initial full string results failing
(so the user needs to try multiple "links")
- plus any punctuation in the string crashes the qry
(try "yoga centre's in London" in his query test page)

And I did indicate in my above post that I was not critical of your response post,
FYI - it does loop 1 time to many, thus generating an empty str1 the last time it loops and also loops unnecessarily on single words
(which would not be useable in a code generated sql qry w/o an error)

My response was more about of Metta's hope a snippet would do it all
- and he wouldn't need to understand or modify anything to get it to work correctly
- or even try to do it himself after some research of his own, and then ask for help in what he tried
That's why my post was pointing out the steps and considerations he would need to go thru to get (your code or any snippet) to work
correctly.

--




| Hi Stefan -
| I think if you re-read what Metta's original post requested, given the example there, my
| snippet does exactly what was asked for. Pare a space delimited string down by a word for
| as many words as there are.
|
| It wasn't intended to be an enterprise grade solution. It was merely to stimulate thought,
| and illustrate some of the basic VBScript usage. Why should the complexity of the solution
| keep someone from answering a post?
|
| MikeR 1st
|
| Stefan B Rusynko wrote:
| > Good luck passing that to a SQL Select query w/o generating an error or w/o looping thru the DB multiple times
| > - and it doesn't parse out the individual words, it only cuts the string down by 1 word each time it loops (not the same thing)
| >
| > Don't get me wrong
| > - I'm not being critical of your attempt to help
| >
| > But Metta is asking for someone to provide a "code snippet" for a rather complex (but doable) series of code that must:
| > a) parse Any user input string (from 0 to X words - say up to 255 characters)
| > b) handle any punctuation in the string that would affect the SQL as in the user string below:
| > "Show me all about a one! Or show me two. What about three? And also one, two, three; plus one or two or three. How about
three
| > two one?"
| > (the punctuation w/ just parsing spaces will cause the LIKE to not find any results)
| > c) handle any SQL key words (will the string allow include and/or processing?)
| > d) based on the parsing generate a single SQL Select for all words plus any words from 1 to X
| > e) and probably not include duplicates to avoid wasted DB loops
| > f) or since the search is using a LIKE avoid possible common words, say "a", to avoid meaningless results
| >
| > IMHO
| > - that is a heck of a lot of "code snippet" to ask for (-;
 
M

Mettá

Well now I have some sense of how far I need to go, any pointers?

How do I stop it looping to many times, how do I get it to remove any "wrong
" characters and how do I include the last word as an option.

For now I do not wish to even attempt to include it in the sql.

Thanks to both of you.

M
 
Ad

Advertisements

S

Stefan B Rusynko

Ok
The below "snippet" contains all the methods you will need to do it correctly
- you will need to expand it to do all the other Required checks to prevent sql injection and errors using the same or similar
methods as already shown in the code
- the code is modular for any table / field / search string or search method

<%
strSearch = "one two three" ' Or get from a form
strField = "FieldName"
strTable = "TableName"
strSbln = " OR "
strStype = "(" & strField & " LIKE '%^%')"
If Len(strSearch) > 0 Then
strSearch = Replace(Trim(strSearch),"'","")
strSearch = Replace(strSearch," "," ")
strWords = Replace(strSearch,"^","")
strSQL = " SELECT * FROM " & strTable & " WHERE " & strStype
strSQL = Replace(strSQL,"^",strWords)
Pos = len(strWords)
While Pos > 0
Pos = InStr(1, strWords," ")
If Pos = 0 Then
strWordx = strWords
If strWordx <> strSearch Then
strSQL = strSQL & strSbln & strStype
strSQL = Replace(strSQL,"^",strWordx)
End IF
Else
strWordx = Ltrim(Mid(strWords,1,Pos-1))
strSQL = strSQL & strSbln & strStype
strSQL = Replace(strSQL,"^",strWordx)
strWords = Ltrim(Mid(strWords,Pos+1,len(strWords)))
End If
Wend
End if
response.write strSQL & "<br>" 'Debug testing only
%>


--




| Well now I have some sense of how far I need to go, any pointers?
|
| How do I stop it looping to many times, how do I get it to remove any "wrong
| " characters and how do I include the last word as an option.
|
| For now I do not wish to even attempt to include it in the sql.
|
| Thanks to both of you.
|
| M
|
| --
| ---
| | >I agree his original wording implies removing a word at a time which is
| >what you have done
| > But as you can see in his implementation test example he has posted, that
| > it is cumbersome approach
| > - he is only removing the trailing word from a LIKE qry
| > (but the word sequence may be critical - it is possible only the middle
| > word will get a result)
| > - and he hasn't been able to auto generate the SQL statement based on the
| > initial full string results failing
| > (so the user needs to try multiple "links")
| > - plus any punctuation in the string crashes the qry
| > (try "yoga centre's in London" in his query test page)
| >
| > And I did indicate in my above post that I was not critical of your
| > response post,
| > FYI - it does loop 1 time to many, thus generating an empty str1 the last
| > time it loops and also loops unnecessarily on single words
| > (which would not be useable in a code generated sql qry w/o an error)
| >
| > My response was more about of Metta's hope a snippet would do it all
| > - and he wouldn't need to understand or modify anything to get it to work
| > correctly
| > - or even try to do it himself after some research of his own, and then
| > ask for help in what he tried
| > That's why my post was pointing out the steps and considerations he would
| > need to go thru to get (your code or any snippet) to work
| > correctly.
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > To find the best Newsgroup for FrontPage support see:
| > http://www.net-sites.com/sitebuilder/newsgroups.asp
| > _____________________________________________
| >
| >
| > | > | Hi Stefan -
| > | I think if you re-read what Metta's original post requested, given the
| > example there, my
| > | snippet does exactly what was asked for. Pare a space delimited string
| > down by a word for
| > | as many words as there are.
| > |
| > | It wasn't intended to be an enterprise grade solution. It was merely to
| > stimulate thought,
| > | and illustrate some of the basic VBScript usage. Why should the
| > complexity of the solution
| > | keep someone from answering a post?
| > |
| > | MikeR 1st
| > |
| > | Stefan B Rusynko wrote:
| > | > Good luck passing that to a SQL Select query w/o generating an error
| > or w/o looping thru the DB multiple times
| > | > - and it doesn't parse out the individual words, it only cuts the
| > string down by 1 word each time it loops (not the same thing)
| > | >
| > | > Don't get me wrong
| > | > - I'm not being critical of your attempt to help
| > | >
| > | > But Metta is asking for someone to provide a "code snippet" for a
| > rather complex (but doable) series of code that must:
| > | > a) parse Any user input string (from 0 to X words - say up to 255
| > characters)
| > | > b) handle any punctuation in the string that would affect the SQL as
| > in the user string below:
| > | > "Show me all about a one! Or show me two. What about three? And
| > also one, two, three; plus one or two or three. How about
| > three
| > | > two one?"
| > | > (the punctuation w/ just parsing spaces will cause the LIKE to not
| > find any results)
| > | > c) handle any SQL key words (will the string allow include and/or
| > processing?)
| > | > d) based on the parsing generate a single SQL Select for all words
| > plus any words from 1 to X
| > | > e) and probably not include duplicates to avoid wasted DB loops
| > | > f) or since the search is using a LIKE avoid possible common words,
| > say "a", to avoid meaningless results
| > | >
| > | > IMHO
| > | > - that is a heck of a lot of "code snippet" to ask for (-;
| >
| >
|
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top