Sort Order and case sensitivity

[Steve]

I have a query which filters records containing uppercase and
Lowercase i.e.

Smith and SMITH, Henderson and HENDERSON etc.

Is there a way that I can filter only those records that contain the
first uppercase letter and the remaining lowercase letters for my
query i.e. Smith , HENDERSON etc.

Thanks

[Erland Sommarskog]

Steve (mu******@hotmail.com) writes:

I have a query which filters records containing uppercase and
Lowercase i.e.

Smith and SMITH, Henderson and HENDERSON etc.

Is there a way that I can filter only those records that contain the
first uppercase letter and the remaining lowercase letters for my
query i.e. Smith , HENDERSON etc.

You can do this (example runs in Northwind):

SELECT *
FROM Customers
WHERE CompanyName COLLATE Latin1_General_BIN LIKE '[A-ZÀ-Ý]%'
AND CompanyName COLLATE Latin1_General_BIN NOT LIKE '_%[A-ZÀ-Ý]%'

The requirements is somewhat relaxed here. You will actually get
hits for "J1234" or "D....". Depending on your data, this may or
may not help.

Please note that it is not likely that SQL Server will use an index
for this search.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve]

Thanks for the tip, I have had a problem in trying to using it in
SQL2000 as I get an error regarding the COLLATE function. My database
in case insensitive and accent insensitive, I'm wondering if this may
have something to do with it.

Regards

Steve

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

Steve (mu******@hotmail.com) writes:

I have a query which filters records containing uppercase and
Lowercase i.e.

Smith and SMITH, Henderson and HENDERSON etc.

Is there a way that I can filter only those records that contain the
first uppercase letter and the remaining lowercase letters for my
query i.e. Smith , HENDERSON etc.

You can do this (example runs in Northwind):

SELECT *
FROM Customers
WHERE CompanyName COLLATE Latin1_General_BIN LIKE '[A-ZÀ-Ý]%'
AND CompanyName COLLATE Latin1_General_BIN NOT LIKE '_%[A-ZÀ-Ý]%'

The requirements is somewhat relaxed here. You will actually get
hits for "J1234" or "D....". Depending on your data, this may or
may not help.

Please note that it is not likely that SQL Server will use an index
for this search.

[Erland Sommarskog]

Steve (mu******@hotmail.com) writes:

Thanks for the tip, I have had a problem in trying to using it in
SQL2000 as I get an error regarding the COLLATE function. My database
in case insensitive and accent insensitive, I'm wondering if this may
have something to do with it.

Rather than saying that you get an error, it would be somewhat easier
for me to say what is the problem, if you also included the error message.
Of course, also the exact statement you are using would be helpful.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve]

Hi

Here is the error I recieve when trying to run the code in SQL
[Microsoft][ODBC SQL Sever Driver][SQL Server]Line 1 : Incorrect
syntax near 'COLLATE'

Regards

Steve

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Steve (mu******@hotmail.com) writes:

Thanks for the tip, I have had a problem in trying to using it in
SQL2000 as I get an error regarding the COLLATE function. My database
in case insensitive and accent insensitive, I'm wondering if this may
have something to do with it.

Rather than saying that you get an error, it would be somewhat easier
for me to say what is the problem, if you also included the error message.
Of course, also the exact statement you are using would be helpful.

[Erland Sommarskog]

Steve (mu******@hotmail.com) writes:

Here is the error I recieve when trying to run the code in SQL
[Microsoft][ODBC SQL Sever Driver][SQL Server]Line 1 : Incorrect
syntax near 'COLLATE'

Since you did not provide the statment, I will have to guess. Assuming
that you used the statement that I gave as example, my guess is that
your database is not at compability level 80. COLLATE was added to
SQL 2000, so if your database is set at backward compatinility, COLLATE
is not available.

You can use sp_dbcmplevel to both determine the compatibility level and to
change it.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve]

Hi,

The compability level was 70.

Thanks

Steve

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Steve (mu******@hotmail.com) writes:

Here is the error I recieve when trying to run the code in SQL
[Microsoft][ODBC SQL Sever Driver][SQL Server]Line 1 : Incorrect
syntax near 'COLLATE'

Since you did not provide the statment, I will have to guess. Assuming
that you used the statement that I gave as example, my guess is that
your database is not at compability level 80. COLLATE was added to
SQL 2000, so if your database is set at backward compatinility, COLLATE
is not available.

You can use sp_dbcmplevel to both determine the compatibility level and to
change it.