Jeroen Mostert
9/10/2008 5:34:00 PM
Richard Everett wrote:
> I have a table in SQL server containing records that are uniquely
> identified using a three character alphanumeric code.
>
> Codes are either all letters (eg ABC) or all numeric (eg 123)
>
> I need to obtain only the records whose three character code is all
> letters.
>
> In SQL I can achieve this using:
>
> SELECT [Code]
> -- and other fields
> FROM [dbo].[TableName]
> where [Code] > '999'
>
This is a hack; knowing that the letters will sort lexically after the
numbers relies on the collation. Are you deliberately avoiding LIKE because
you want to take advantage of a range index? That is, a much less cryptic
query would use WHERE [Code] LIKE '[A-Z][A-Z][A-Z]' (three letters) or WHERE
[Code] NOT LIKE '[0-9][0-9][0-9]' (anything that's not three digits).
In fact, if you *know* codes are either all-numbers or all-letters, you can
express this in the table itself:
ALTER TABLE Codes ADD
IsNumericCode AS CAST(CASE WHEN [Code] LIKE '[0-9][0-9][0-9]' THEN 1 ELSE 0
END AS BIT),
IsLetterCode AS CAST(CASE WHEN [Code] LIKE '[A-Z][A-Z][A-Z]' THEN 1 ELSE 0
END AS BIT);
Now you can search on these computed fields to your heart's content.
> How can I do this using LINQ to SQL?
>
Well, actually, why can't you do it just as in SQL?
from code in Codes
where code > "999"
select code;
This should translate to the statement above.
I know of no way to write it in such a way that the appropriate LIKE query
is generated; patterned LIKE to my knowledge has no LINQ to SQL pendant.
> I've already tried writing a custom C# function that checks to see if
> the code can be parsed as an integer, but that won't translater to SQL
> (which is understandable).
To avoid *that*, you can force query evaluation (and switch to LINQ to Objects):
from code in Codes.ToArray()
where myComplicatedCondition(code)
select code;
Obviously, this will retrieve all records in the table first, so if you're
just looking for a few records this is horribly inefficient.
--
J.