How do I search the contents of all fields in a query result set?

Go To StackoverFlow.com

3

I'm trying to create a general search feature that will search the contents of every field in a result set.

So for instance, if I were to write a function in TSQL that returns a result set that contains 100 rows and the fields TicketID, FirstName, LastName and Comment I want to search each one of those fields for each record for the string 'Mike'.

So essentially if the word 'Mike' is in any of those fields, that record will be included in my search.

How do I do this?

2012-04-03 20:42
by Sinaesthetic


2

You could create a full Full Text Index. You can have this Index for a whole table or selected columns in the table via a view. http://msdn.microsoft.com/en-us/library/ms142575.aspx#examples It has it own syntax but it is part of the TSQL query and can be combined with regular column search.

2012-04-03 21:59
by paparazzo


1

from x in someTableOrQuery
where x.FirstName.Contains(str) || x.LastName.Contains(str) || ...
select x

This is just normal C#. No tricks required.

Note that this will translate to a LIKE-expression in SQL. But you won't need to do manual string concatenation which is prone to bugs (not SQL injection in this case).

2012-04-03 20:58
by usr
x is not define - paparazzo 2012-04-04 01:58
I improved the sample code - usr 2012-04-04 09:17


1

Or you can use the SqlMethods.Like method, so you are able to search your expression for a specific pattern.

Something like this:

where SqlMethods.Like(str,"%Mike%");

Hope this helps!

2012-04-03 21:06
by Rob Angelier


1

The entry for that method would be a DataTable with the resulting set of query's execution. Then you need to iterate on the rows and columns in this way:

foreach (DataRow dr in yourDataTable.Rows)
{
    foreach(DataColumn col in yourDataTable.Columns)
    {
        try
        {
            if (dr[col].ToString().Contains("Mike"))
            {
                //Save this one!
            }
        }
        catch
        { 
            //it was not a string, go on then.
        }
    }
}
2012-04-03 21:39
by daniloquio