need to make a custom function to split a sentence into words

[Atomic Shrimp]

I want to construct a custom function that splits a sentence up into words and builds a WHERE clause that I can embed into a SQL statement.

It is to do smart searching on a book titles database, so if the user enters:
CAT DOG TRAIN

the function would convert this to:

WHERE (((BOOKS.TITLE) Like '*CAT*' And (BOOKS.TITLE) Like '*DOG*' And (BOOKS.TITLE) Like '*TRAIN*'))

but if they enter a sentence with only two words:
CAT DOG

the function would return:
WHERE (((BOOKS.TITLE) Like '*CAT*' And (BOOKS.TITLE) Like '*DOG*')

My idea is to do it like this:
-trim the string(to lose spare spaces from the end)
-essentially replace spaces within the string with:
*' And (BOOKS.TITLE) Like '*

then add to the start of the string:
WHERE (((BOOKS.TITLE) Like '*

and add to the end:
*')

I'm sure this will work, but is there an easier way?

Mike

 

[Fornatian]

I would think the only way to do it would be to loop through a search string adding one character at a time until you hit a " " then record that word in a string array(sorry but I think some complicated looping mechanisms and dynamic Left(), Right(), Mid() functions might be required.)

Continue doing this until you've a complete string array - then loop through the array creating your desired string.

I can't provide the code because I don't know anything about string arrays except that they can hold more than one string but I think I'm on the right lines.

If you have any luck OR SOMEONE ELSE HAS A BETTER ANSWER? will you please post it as I would like the answer too.

Cheers

Ian

 

[Atomic Shrimp]

I had a little play and it wasn't as bad as I thought, here's the function I came up with:

Public Function makeSQL(inputstr As String)
inputstr = Trim(inputstr)
'get rid of any double spaces
While InStr(inputstr, " ") > 0
inputstr = Left(inputstr, InStr(inputstr, " ") - 1) & Mid(inputstr, InStr(inputstr, " ") + 2)
'NB the " " contains TWO spaces in the above lines
Wend

'build the middle of the where clause one word at a time
While InStr(inputstr, " ") > 0
makeSQL = makeSQL & Left(inputstr, InStr(inputstr, " ") - 1) & "*' And (Titles.TITLE) Like '*"
inputstr = Mid(inputstr, InStr(inputstr, " ") + 1)
'NB the " " contains ONE space in the above lines

Wend

'add the rest of the SQL statement
makeSQL = UCase("SELECT Titles.ISBN, Titles.CATALOGUE, Titles.TITLE, Titles.PUBPRICE1, Titles.TTLAUTHOR FROM Titles WHERE (((Titles.TITLE) Like '*" & makeSQL & inputstr & "*'));")

End Function

It works perfectly, but I think it could probably be simplified further.

Mike

[This message has been edited by Mike Gurman (edited 12-22-2000).]

 

[Fornatian]

Cheers Mike,

I think I'll cut and paste that one for the scrap book!

Ian

 

[BarkerD]

An easy way to build the SQL clause would be to use the IN keyword.

create a string variable and build it with delimiters and the book titles.

I would use a loop to build the string.

Dim inputstr As String
'Add an extra space to capture last data item
inputstr = inputstr & " "

Dim strParameters As String
While InStr(inputstr, " ") > 0
strParameters = strParameters & "," & Chr(34) & Left(inputstr, InStr(inputstr, " ") - 1) & Chr(34)
inputstr = Mid(inputstr, InStr(inputstr, " ") + 1)
Wend

'Chop off the leading ","
strParameters = Mid(strParameters, InStr(strParameters, ",") + 1)

<EDIT> Just thought of something.
This will probably not work when using the LIKE clause. Hmm how to get around that. <END EDIT>

Your SQL clause would look like:

strSQl="SELECT Titles.ISBN, Titles.CATALOGUE, Titles.TITLE, Titles.PUBPRICE1, Titles.TTLAUTHOR FROM Titles WHERE Titles.TITLE IN (strParameters);"


[This message has been edited by BarkerD (edited 12-21-2000).]

[This message has been edited by BarkerD (edited 12-21-2000).]