473,581 Members | 2,757 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 9912
Steve (mu******@hotma il.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******@hotma il.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******@hotma il.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******@hotma il.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******@hotma il.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******@hotma il.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

32
3140
by: Elliot Temple | last post by:
Hi I have two questions. Could someone explain to me why Python is case sensitive? I find that annoying. Also, why aren't there multiline comments? Would adding them cause a problem of some sort? Thanks, Elliot
4
3734
by: its me | last post by:
Let's say I have a class of people... Public Class People Public Sex as String Public Age as int Public Name as string end class And I declare an array of this class...
761
28288
by: Neo-LISPer | last post by:
Hey Recently, I researched using C++ for game programming and here is what I found: C++ game developers spend a lot of their time debugging corrupted memory. Few, if any, compilers offer completely safe modes. Unsurprisingly, there is a very high failure rate among projects using C++ for modern game development.
16
2907
by: Starwiz | last post by:
I'm a VB.net programmer, and I'm about to start working with two C++ programmers and teach them .net. I've decided to use C# in teaching them, since it's similar enough to VB.net that I can read and write it, and it's what's most familiar to them. My major problem with C#, however, is its case- sensitivity. I've heard people talk about...
3
1147
by: Jason Tesser | last post by:
I am converting data from Access into Postgres and ran into an issue with case sensitivity. Can I write queries in Access that will be case insensitive without rewriting the queries. So I would like to know if this be handled in Postgres or even if someone knows in Access. Thank you.
14
2320
by: Christian Sell | last post by:
Hello, I am running into a problem with PGs case sensitivity with regard to column and table names. I am using program components that require the object names returned from database metadata queries to be in uppercase. Therefore, I am forced to use double quotes in the table creation scripts, like create table "BLA" (); However, after...
3
4251
by: Anita Potekkat | last post by:
Hello, I had a question regarding Case Sensitivity in 10g & 9i. (1) Does Case Sensitivity in Oracle have to do with data only? Or does it also effect table & column names? For e.g. in a table NAMES with column NAME are the following queries equivalent regardless of whether case sensitivity is turned on or off. select name from names;...
2
8443
by: sweetpotatop | last post by:
Hi, I believe my SQL server was configured as Case sensitivity. I have a number of stored procedures which were moved from a non-Case sensitivity SQL server. Because of the Case sensitivity, I have to do a lot of editing in those stored procedures. Is there a quick way to avoid the editing? Something like ignoring the case in one...
3
5173
by: aRTx | last post by:
I have try a couple of time but does not work for me My files everytime are sortet by NAME. I want to Sort my files by Date-desc. Can anyone help me to do it? The Script <? /* ORIGJINALI
0
7804
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8310
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7910
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8180
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5681
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3809
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3832
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2307
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1144
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.