How do I query for when a field doesn't begin with a letter?

I'm tasked with adding an option to our search, which will return results where a given field doesn't begin with a letter of the alphabet. (The .StartsWith(letter) part wasn't so hard).

But I'm rather unsure about how to get the开发者_运维知识库 results that don't fall within the A-Z set, and equally hoping it generates some moderately efficient SQL underneath.

Any help appreciated - thanks.

In C# use the following construct, assuming db as a data context:

var query = from row in db.SomeTable
            where !System.Data.Linq.SqlClient.SqlMethods.Like(row.SomeField, "[A-Z]%")
            select row;

This is only supported in LINQ to SQL queries. All rules of the T-SQL LIKE operator apply.

You could also use less effective solution:

var query = from row in db.SomeTable
            where row.SomeField[0] < 'A' || row.SomeField[0] > 'Z'
            select row;

This gets translated into SUBSTRING, CAST, and UNICODE constructs.

Finally, you could use VB, where there appears to be a native support for the Like method.

Though SQL provides the ability to check a range of characters in a LIKE statement using bracket notation ([a-f]% for example), I haven't seen a linq to sql construct that supports this directly.

A couple thoughts:

First, if the result set is relatively small, you could do a .ToList() and filter in memory after the fact.

Alternatively, if you have the ability to change the data model, you could set up additional fields or tables to help index the data and improve the search.

--EDIT--

Made changes per Ruslan's comment below.

Well, I have no idea if this will work because I have never tried it and don't have a compiler nearby to try it, but the first thing I would try is

var query = from x in db.SomeTable
            where x.SomeField != null &&
                  x.SomeField.Length >= 1 &&
                  x.SomeField.Substring(0, 1).All(c => !Char.IsLetter(c))
            select x;

The possiblility exists that LINQ to SQL fails to convert this to SQL.