How to query records that begin with lower case characters?

I have edited a single company name in the Northwoods Customers
table so the CompanyName value begins with a lower case character.
When using Query Analyzer to run against Northwoods the following
query returns 4 records: the one I edited with the lower case a and 3
others where the company's name begins with upper case A
SELECT * FROM Customers
WHERE CompanyName LIKE 'a%'
Thus, case insensitivity appears to be the T-SQL default. How would
I get only those records where CompanyName begins with lower case
characters?
BTW: I'm building one of those alphanumerice linked character menus
that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
have been required to use a T-SQL range parameter, i.e. [Aa]% to get
*all* company names whether they begin with an upper case letter
or a lower case letter.
Using [Aa]% functioned as described above but then I realized I misled
myself as I was not able to get *only* records with a company name that
began with the lower case character when using [a]% or a%.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
With SQL 2000, you can specify a case-sensitive collation:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
In order to use a CompanyName effectively, you can also include the
case-insensitive criteria:
SELECT *
FROM Customers
WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
AND CompanyName LIKE 'a%'
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
> I have edited a single company name in the Northwoods Customers
> table so the CompanyName value begins with a lower case character.
> When using Query Analyzer to run against Northwoods the following
> query returns 4 records: the one I edited with the lower case a and 3
> others where the company's name begins with upper case A
> SELECT * FROM Customers
> WHERE CompanyName LIKE 'a%'
> Thus, case insensitivity appears to be the T-SQL default. How would
> I get only those records where CompanyName begins with lower case
> characters?
> BTW: I'm building one of those alphanumerice linked character menus
> that look like this: 0-1 A B C D E F G H... X Y Z and thought I might
> have been required to use a T-SQL range parameter, i.e. [Aa]% to get
> *all* company names whether they begin with an upper case letter
> or a lower case letter.
> Using [Aa]% functioned as described above but then I realized I misled
> myself as I was not able to get *only* records with a company name that
> began with the lower case character when using [a]% or a%.
>
> --
> <%= Clinton Gallagher
> A/E/C Consulting, Web Design, e-Commerce Software Development
> Wauwatosa, Milwaukee County, Wisconsin USA
> NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>
|||Yes that did help by identifying the grammar I can use to do more
study. Thank you.
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@. REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#hGd6AxVEHA.584@.TK2MSFTNGP09.phx.gbl...
> With SQL 2000, you can specify a case-sensitive collation:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> In order to use a CompanyName effectively, you can also include the
> case-insensitive criteria:
> SELECT *
> FROM Customers
> WHERE CompanyName LIKE 'a%' COLLATE SQL_Latin1_General_CP1_CS_AS
> AND CompanyName LIKE 'a%'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" <csgallagher@.REMOVETHISTEXT@.metromilwaukee.com> wrote in message
> news:ucDJ41wVEHA.2188@.TK2MSFTNGP10.phx.gbl...
>