using regular expression / Remove special characters with linq to sql

Question

How can i use regular expressions with linq to sql in the asp.net mvc(C#) application?

The columns in my MSSQL table (Products) has some special characters like (, - % ',.....).

While searching for a product i need to search without that special chareters.

For ex.: I have a product say (ABC-Camp's / One), when i search for "abccamp", it should pull the product.

I am trying the query like:

from p in _context.pu_Products
     where p.User_Id == userId && p.Is_Deleted == false
     && p.Product_Name.ToLower().Contains(text.ToLower())
     select new Product
     {
           ProductId = p.Product_Id,
           ProductName = p.Product_Name.Replace("’", "").Replace("\"", ""),
           RetailPrice = p.Retail_Price ?? 0M,
           LotSize = p.Lot_Size > 0 ? p.Lot_Size ?? 1 : 1,
           QuantityInHand = p.Quantity_In_Hand ?? 0  
     }

But i need to search without any special characters...

Answer 1

Eh, I think you have to check all of chars in records. 'cause it cannot be with a text. like this.

Linq2Sql:

IQueryable<Product> iQueryable = from p in _context.pu_Products
                                 where p.User_Id == userId && p.Is_Deleted == false
                                 select p;

Match match = Regex.Match(text, "(?<Str>[\\w])+")
if (match.Success) {
    foreach (Capture capture in match.Groups["Str"].Captures) {
        string value = capture.Value;
        iQueryable = iQueryable.Where(p => p.Product_Name.Contains(value));
    }
}

var products = from p in iQueryable
               select new Product
               {
                    ProductId = p.Product_Id,
                    ...
               }

Generated SQL:

WHERE Product_Name LIKE '%a%' AND Product_Name LIKE '%b%' AND...

Answer 2

Here are 2 options:

1. Using the SqlMethods.Like method (recommended)
2. Using the AsEnumerable method to pull down the records and use regex or such on the client side in memory (costlier)

Going with option #1 above, you could do something like this:

string searchTerm = "abccamp";
string pattern = "%"
                 + String.Join("%", searchTerm.Select(c => c.ToString()).ToArray())
                 + "%";

var query = from x in y
            where SqlMethods.Like(x, pattern)
            select x;

Adapting it to your query you would probably use something similar to SqlMethods.Like(p.Product_Name, pattern) in place of p.Product_Name.ToLower().Contains(text.ToLower()).

EDIT: as mentioned in the comments this approach may not yield accurate results since a wildcard exists between each character to take the place of the special characters. Another possible approach is to use _ instead of % to account for a single character. The downside is you might have multiple special characters in a row and a wildcard would be needed for repetitions, which puts us back where we started. With SqlMethods.Like you can use the same things available to a TSQL LIKE. This includes character classes with ranges and negated character classes, so you could also use [!/@ ] in place of the % in String.Join.

The problem with using _ is that you can't make the single character optional as you would do with a regex. A pattern of a_b_c_d doesn't match "abcd" since each _ must match a character in between each letter. In other words, it would match "a!b$c@d". Perhaps you can use both in the where clause: ... where x == searchTerm || SqlMethods.Like(x, pattern) where pattern is the same as before but using a _ instead of % in the String.Join method.

Additionally, be sure to have some if/else logic checks around the searchTerm to determine what to do if the input is empty or consists of a single character which would yield %x% and may not be desirable and match many rows.

Answer 3

No you can't, there is no transformation from a regex to a SQL query.

What you can do is perhaps use the ExecuteQuery<> operation (using a baked SQL literal) on the DataContext, and install a .Net function (UDF) into the database that performs the regex (no unsafe stuff required, as Regex is defined in the core libraries).

That way you get to do it all on the server.

To simplify, and get rid of most of the embedded SQL, you could use GetCommand from the DataContext, and pass your current Linq statement in with all but the Regex condition. Get the text back, and add the Regex to the where clause (again, you still need a regex UDF in the database).

I'm also not sure if the string.Replace method call is going to translate properly to SQL either - in which case you might need to use a UDF for that in the select statement as well.