469,111 Members | 1,934 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

Sort Order and case sensitivity

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
Jul 20 '05 #1
6 9629
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
Jul 20 '05 #2
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.

Jul 20 '05 #3
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
Jul 20 '05 #4
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.

Jul 20 '05 #5
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
Jul 20 '05 #6
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.

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

32 posts views Thread by Elliot Temple | last post: by
4 posts views Thread by its me | last post: by
761 posts views Thread by Neo-LISPer | last post: by
3 posts views Thread by Jason Tesser | last post: by
14 posts views Thread by Christian Sell | last post: by
2 posts views Thread by sweetpotatop | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.