DevExpertise

One of the requirements of one of my recent projects was to implement a search page which allowed the user to enter a search term that supported wildcards.  The search term could contain any number of wildcards in any position within that term.

If you’ve done anything like this before, you probably know there’s nothing built-in to LINQ that supports this type of behavior.  Sure, you could use a combination of String.StartsWith, String.EndsWith, or String.Contains, but this could quickly become too cumbersome if there are many wildcards and/or they are scattered throughout the search term.  Let’s look at a couple simple examples to illustrate…

Pretend for a second I was doing this in SQL, and I needed to get all values that start with the letter T.  I would do this:

select * from SomeTable where SomeField LIKE 'T%'

The .NET/LINQ equivalent would be this:

var results = (from v in values where v.StartsWith("T") select v);

Not too difficult.  However, what if you wanted to do the SQL-equivalent of this:

select * from SomeTable where SomeField LIKE '%a%a%'

You’d have do a little creative parsing.  It gets even worse when you as the developer doesn’t know what search term will be entered, how many wildcards will be included, and where in the term they appear.  It all has to be dynamic.

I did a little poking around to see if anyone has done this before, and the only thing I could find was recommendations on using StartsWith/EndsWith/Contains, which I already ruled out.  I also found the SqlMethods.Like() method which sounded perfect.  However after further research, discovered it can only be used on an entity directly retrieved from a DataContext, such as this:

using (DemoDataContext db = new DemoDataContext()){
    var results = (from v in db.SomeTable where SqlMethods.Like(v.SomeField, "*a*a*") select v);
}

If you try to use the SqlMethods.Like() method on anything except a DataContext’s Table<T>, you’ll get the following message:

“Method ‘Boolean Like(System.String, System.String)’ cannot be used on the client; it is only for translation to SQL.”

So much for that.  I decided to write my own extension method.  I figured I could write one fairly easily using a regular expression, and I was right!  I checked out a trusty RegEx cheat sheet and found the following relevant metacharacters:

• ^     Indicates the start of a string
• $     Indicates the end of a string
• *     Indicates zero or more of previous expression

Knowing this, I wrote the following extension method:

public static bool Like(this string value, string term) {
    Regex regex = new Regex(string.Format("^{0}$", term.Replace("*", ".*")), RegexOptions.IgnoreCase);
    return regex.IsMatch(value ?? string.Empty);
}

Which I can then use like this:

var results = (from v in values where v.Like("*a*a*") select v);

I can even simplify this by wrapping it up in another extension method:

public static IEnumerable<string> Like(this IEnumerable<string> source, string expression) {
    return (from s in source where s.Like(expression) select s);
}

Now all I have to do is the following:

var results = values.Like("*a*a*");

Finally, a quick usage example to prove it works:

var values = new List<string>(){
    "Widget", "Gadget", "Whatchamacallit", "Gizmo",
    "Thingamabob", "Thingamajig", "Doodad", "Doohickey"};

var results = values.Like("*a*a*");

foreach (string result in results) {
    Console.WriteLine(result);
}

Which outputs:

Hopefully you’ll find this useful!

Once in awhile I run across a need to create a delimited list from a set of rows in a SQL table.  The old-school method is to use a cursor to iterate over these rows and build a dynamic string.  As we all know, cursors are expensive and should be avoided whenever possible.  I recently found a better way to accomplish this and wanted to share it in case you’ve run into this before.

For this example, I’m going to be working with a simple Cities table that contains a City field and a State field.  A simple SELECT statement yields the following result:

select * from Cities where State = 'New York'

The first step is to transform this result into a chunk of XML, and for that the FOR XML statement is perfect.  Consider the following SQL:

declare @xml varchar(1000)
set @xml = (select City from Cities where State = 'New York' for xml path(''))

This returns the following (I trimmed it down a little to fit on the page, but it does return all rows):

<City>New York City</City><City>Buffalo</City><City>Rochester</City>

Now that we have an XML string, we can just use the built-in REPLACE functions to remove the XML nodes:

set @xml = replace(@xml, '<City>', ',')
set @xml = replace(@xml, '</City>','')

This returns the following:

,New York City,Buffalo,Rochester,Yonkers,Syracuse

Now, the only thing left is to remove the beginning space and comma from this for which the new SQL 2008 STUFF function is perfect:

select stuff(@xml,1,1,'')

This returns the following, which is exactly what we need:

New York City,Buffalo,Rochester,Yonkers,Syracuse

Now, chances are you don’t want to have to declare variables and do all this in multiple operations.  This can all be wrapped into a single select statement, which makes it easy to implement:

select stuff((replace(replace(
       (select City from Cities where State = 'New York' for xml path('')), '<City>', ','
       ), '</City>','')),1,1,'')

That’s it!  Pretty slick, huh?