SQL problem

I am new to SubSonic.  I have a statement that looks at a field  called Search.  I would like my users to get results for individual words they input in it.  For example  they enter  "white cars".  I want them to get records that contain "white" and records that contain "cars".  If I find "white cars" that would be ok also.  I just don't want to limit them to search only for "white cars" as it is the case now.  Can you please suggest what I could do?

thanks,

g

There are so many things to consider here and so many ways to do this.

Does this help you get started? its certainly not a good solution, and its completely untested, but it might give you a start point.

Dim words As String = "small white car"
Dim Q As New SubSonic.Query("MyTable")
Dim nCounter As Integer = 0
For Each word As String In Split(words, " ")
    If nCounter = 0 Then
       Q.AddWhere("MyColumn", SubSonic.Comparison.Like, "'%" & word & "%'")
    Else
       Q.OR("MyColumn Like '%" & word & "%'")
    End If
    nCounter+=1
Next
Dim ds As DataSet = Q.ExecuteDataSet

static void Main(string[] args)
        {
            string myString = "super slow white cars";
            string[] partsOfMyString = myString.Split(' ');
            int sqlNumber=0;
            foreach (string part in partsOfMyString)
            {
                Console.WriteLine(part);
                sqlNumber++;
                Console.ReadKey();
            }
            Console.WriteLine(sqlNumber.ToString());         

//  I will write my sql statement here and use a dynamically created query based on the number of words found.  I will post the finished code for someone's future reference. 

            Console.ReadKey();

}

similar to what I had thought except that I didn't know how to write the subsonic syntax.  thank you this helps

Bear in mind, the use of the wildcards "%" on both sides of the equation eliminates index useage and will force the query to perform a full table scan.  Depending on the size of the field and the number of rows, this could take a considerable amount of time. 

DiverKas:

Bear in mind, the use of the wildcards "%" on both sides of the equation eliminates index useage and will force the query to perform a full table scan.  Depending on the size of the field and the number of rows, this could take a considerable amount of time. 

Thanks for teaching best-practice!

Hi,

And if you are using SQL Server you may also look at FullText indexes and their query syntax to have a more flexible search that will use an index.

/jonny