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 (-;
| >
| >
|
|
