I want to select on the value of a portion of a field, rather than the whole
field, and I want it to be case insensitive.
For example, let's say I'm searching on field A1, and I want to find all
records where the first 7 characters (regardless of case) of A1 are
"PRODUCT". How can I do this? I've seen this before, but haven't done this
often enough to remember.

where left(field,7) ='PRODUCT'
Should be case insensitive, if not use
where UPPER(left(field,7)) ='PRODUCT'

------------------------------------------------------------------------------------------
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

[quoted text, click to view]

Hi Leo,

If either your database or the column is set up with a case insensitive
collation, then the easiest and best performing way is to use

WHERE A1 LIKE 'product%'

If your database and/or the column has a case sensitive collation,
you'll have to force the use of a different collation here:

WHERE A1 COLLATE Latin1_General_CI_AI LIKE 'product%'


Best, Hugo
--