parsing search text

[j9]

How would we write code to parse statements to account for various user entered search criteria, similar to the searches you can do with Google?

For example:
If the search is for a Title and the user enters: "Component Developer" Magazine, how would we write the sql code to search for
titles containing "Component Developer" AND "Magazine" ? Etc.

 

[henslecd]

For your specific statement that you just asked.

Select *fields that you want to display* from table1 where title = "Component Developer" AND title = "Magazine"

Of course what you really were asking is how can there be a search or all those words.

Select *fields that you want to display* from table1 where title Like "%Component%" OR title LIKE "%Developer%" OR title LIKE "%Magazine%"

That searches for those words anywhere in the title string. Taking the % off in front means the title would begin with the search criteria, taking it off at the end means it would end with search criteria.

 

[j9]

I think what I want returned is:
Select *fields that you want to display* from table1 where title like "%Component Developer%" AND title like "%Magazine%". The title should contain all terms in the search criteria and should search for full terms if the user put it in quotes, e.g. 'Component Developer'.

I asked my question wrong. What I really want to know is how to parse the search text in C# in order to create the sql statement.

 

[henslecd]

Note: This is not tested, and the syntax is probably not perfect. Should give you a start.

Dim sSql as string
Dim sTitle as string

sSql = "Select *fields that you want to display* from table1"

'send text to a function for parsing
sTitle = someFunc("title", textbox1.text)

Function someFunc(field as string, searchString as string)

'Write Logic in here
'I don't have time to figure out logic for enclosed ""
'but for the spaces, I would replace with AND
'if the search string was Component Developer Magazine

Replace(searchString, " ", "AND")

'that returns Component AND Developer AND Magazine
'so then i would add in the field criteria

searchString = field & "=" & Replace(searchstring, "AND ", "AND " & field & "=")

'that returns title = Component and title = developer and title = Magazine
 
return searchString

End Function

searchString is then returned.

then you set sSql = sSql & sTitle