I do agree with you BP, but it's a pity SQL Server considers this statement
non-SARGable, because there is no logical reason why it can't create a SARG
in this case.
[substring(au_lname, 1, n) = ...] should be useful to any index that leads
with au_lname. Obviously, anything other than 1 for the substring start is a
different story though..
> In the absence of Umachandar, I'll take on his usual task of pointing out
> that the code you offered is unable to make use of an index on the
> column, if one exists :-)
> > BETWEEN is working correctly.....the sql that you are using is not
> > Observe this example:
> > USE pubs
> > GO
> > SELECT * FROM authors
> > WHERE SUBSTRING(au_lname,1,1) BETWEEN 'a' AND 'd'
> > --
> > Keith, SQL Server MVP
> > > Hello All,
> > > I have been trying to get this where cause to work correctly.
> > > [LastName] >= 'A' AND [LastName] <= 'C'
> > > Unfortunately, it only returns all records that have a last name with
> > > A or B as the first letter. I want it to also return records that
> > > have C as first letter of the last name. Does anyone have any
> > > suggestions on how to get this to work?
> > > Thanks,
> > > Travis