SQL Server Text Matching Is Case INSENSITIVE
Posted May 23, 2007 at 12:33 PM
I am officially retarded. I can't believe that I didn't know that the text equals operator "=" in SQL Server queries was case insensitive. I am not sure where I got this in my head, but I always assumed that "=" was case sensitive for text comparison and LIKE was case INsensitive. But in reality, these three SQL statements all return the same result:
Launch code in new window » Download code as text file »
- SELECT
- id
- FROM
- address
- WHERE
- street1 = '123 Street St'
-
-
- SELECT
- id
- FROM
- address
- WHERE
- street1 = '123 STREET ST'
-
-
- SELECT
- id
- FROM
- address
- WHERE
- street1 = '123 StReEt sT'
Ugggg! To think of the times I have used LOWER() on a field to search for case insensitive text matches. Thank God I don't deal with too many queries that needed to do case insensitive searches (at least the fallout of my ignorance will not wreak too much havoc).
I just hate making mistakes like that or finding such glaring knowledge gaps :(
Download Code Snippet ZIP File
Comments (13) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
CFQuiz 2.13: Selecting N Random Elements From An Array In ColdFusion
Zipping All Code Snippets With ColdFusion Zip Utility Component
Reader Comments
If you are using SQL Server, check out the COLLATE clause to make searches case sensitive....
Posted by Bob Clingan on May 23, 2007 at 12:47 PM
welcome Ben, we've been waiting for you :P
Posted by tony petruzzi on May 23, 2007 at 1:12 PM
If I recall correctly on SQL server 2000, when installing you can decide if your install to be case sensitive can't remember if this is per server or database though.
Posted by kola on May 23, 2007 at 2:01 PM
@Bob,
I will look into that. I have heard of using COLLATE in order to search with extended ASCII characters (I think) but I have never actually used it.
@Tony,
The stars were finally in the right alignment... I have arrived.
@Kola,
Thanks for the heads up. I will talk to my boss about it as he administers the database server installations. And, when I create a new database, I just use the default settings - I have never seen what kinds of things can be set.
Posted by Ben Nadel on May 23, 2007 at 2:25 PM
You might need to change the collation of the database (not reliable in SQL Server 2000) or reinstall SQL Server 2000 with a case sensitive collation.
Take a look at this link:
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054949,00.html
Posted by Dimitar G. on May 23, 2007 at 2:27 PM
@Dimitar,
You are just saying to do that IF I want to change from case insensitive to case sensitive, right?
Posted by Ben Nadel on May 23, 2007 at 2:31 PM
I thought all databases were this way, except for PostGreSQL. I've read that this is one of the big things people /don't'/ like about PGS is that SLQ queries are case sensitive by default. Someone correct me if I'm wrong.
Posted by Jacob Munson on May 23, 2007 at 2:50 PM
By default SQL Server 2000 (and 2005) installs with a case insensitive collation. It's the least problematic anyway but the most restricted in order of dev options like case sensitiveness, alphabetic order, etc.
So I guess when installed your RDBS you left it with its default options. Chances to convert ONLY your db into a case-sensitive collation are high, the opposite is alomost impossible though (from a case-seinsitve db collation to case insensitive db collation).
A good solution (my opinion) is just download SQL Server 2000 Books Online
and read about the installation options. Or goolge, there are plenty of ideas.
The best advice though is simply switch to SQL Server 2005 (if you have an option for this), it's much less problematic, have tons of options to fix such issues without the need to reinstall the server or play around with the system tables, etc. Before that though don;t forget to back up your databases, right :-)
Posted by Dimitar G. on May 23, 2007 at 3:55 PM
Oracle is case sensitive too. Remember, the LIKE clause is for string comparison. It does not deal with case directly, as it is designed to find a string pattern. In fact, LIKE should be case sensitive. Honestly, I feel that all RDBMS should process everything with case sensitivity. This can help you find poorly formatted or erroneous data. jmtc.
Posted by Mark Cadle on May 23, 2007 at 9:22 PM
You don't need to reininstall to do a case sensitve search. Here is an example:
SELECT Col1, Col2, Col3
FROM MyTable
WHERE Col3 COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%foo%'
Here you set Col3 case sensitive. You can also use the "=" statement in stead of LIKE.
Posted by Jorrit on May 24, 2007 at 8:08 AM
I see that this COLLATE stuff has a lot of power. I need to look into that more.
Posted by Ben Nadel on May 24, 2007 at 8:12 AM
You can also make the comparison on a varbinary conversion of the strings
WHERE convert(varbinary, column1) = convert(varbinary, @param)
Seen here
http://www.devx.com/tips/Tip/13043
Posted by Olivier H on Aug 1, 2007 at 9:50 AM
Thanks Jorrit very interesting and straight forward answer worked for me like a charm
Posted by John on Sep 13, 2007 at 1:52 PM
