none
SQL LIKE 'foo%bar' to LINQ? RRS feed

  • Question

  • Hi,

     

    I need wildcards in my search form. If the user types "foo*bar*foo" than I want a SQL like this: WHERE [name] LIKE 'foo%bar%foo'.


    There are lambda expressions for 'foo%' (.StartsWith), '%foo' (.EndsWith) and '%foo%' (.Contains), but what about 'foo%bar%foo1%bar2'?

     

    I tried to .Split('*') the input, than use some .Where() in a foreach, but it did not worked.

     

    What am I missing?

    Monday, July 21, 2008 2:25 AM

Answers

All replies

  • You can also use the SqlMethods.Like() method to get exact SQL like behavior.

     

    Monday, July 21, 2008 4:23 PM
    Moderator
  • You can use SqlMethods.Like( ) for pattern-matching support, as shown below:

     

    Code Snippet

    string pattern = "%Foo%";

    var query =

            from x in myData

            where SqlMethods.Like(x.Name, pattern)

            select x;

     

     

     

    Thanks,

     

    --Samir

    Monday, July 21, 2008 4:26 PM
  • Thank you, you almost replied simultaneously Smile

    I didn't mark Matt's answer, it was already marked when I came here. Is it normal?

     

    I have more interest in this:

    I have a table with 4 records, the column I want to query has the following values:

    test1

    test2

    teszt1

    teszt2

    With this pattern: 't%s%', the query returns only the first 2 rows.

    I also tried this pattern: 't%t%' which returns all 4 records.

    Why?

    Monday, July 21, 2008 10:39 PM
  • wow, I think it has something to do with the Hungarian collation. "sz" is one character in the alphabet.

    Monday, July 21, 2008 11:18 PM