22

I just realized that SQL server '=' comparator when used for text comparison is case insensitive. I have a few questions regarding this functionality:

  1. Is this the same for all databases or specific to SQL server?
  2. I have been using the lower function to ensure the text comparison is insensitive till now. Is it still a good idea to follow the same?
  3. How can we do case sensitive comparisons in SQL server?
  4. Why is '=' operator defaulting to case insensitive comparison?
pcofre
  • 3,976
  • 18
  • 27
rkg
  • 5,559
  • 8
  • 37
  • 50
  • I'm pretty sure it's the same for any database with a case-insensitive collation. It's almost if not entirely platform-agnostic (MySQL, SQL Server, PostgreSQL, etc). – BoltClock Feb 08 '11 at 21:55
  • 7
    It can **totally** **BE** case-sensitive - it depends on the **collation** your SQL Server database uses! – marc_s Feb 08 '11 at 21:58
  • 4
    @marc_s: That's so crazy, it might just work :) – OMG Ponies Feb 08 '11 at 21:58
  • note that you can set a collation for a column, so if you have a column that is always going to be case sensitive, you should probably change that rather than always calling `lower` – Jimmy Feb 08 '11 at 21:59

5 Answers5

26

No, case sensitivity has nothing to do with the equals sign.

Case sensitivity is determined by the collation for the database -- see the documentation for details.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Don't happen to have any information regarding what the SQL-92 standard or such say about case-sensitivity and collation? –  Feb 08 '11 at 22:19
  • @pst: Only thing I know about ANSI & collation is the handling of padding & trailing spaces, sorry. – OMG Ponies Feb 08 '11 at 22:27
  • 1
    In Microsoft SQL Server case-sensitivity is determined by the collation of the column(s) concerned and not by the collation of the database itself. – nvogel Feb 08 '11 at 23:14
  • 1
    @dportas: [SQL Server supports collation settings at the exression, column, database, and server/instance levels](http://msdn.microsoft.com/en-us/library/ms143508%28SQL.90%29.aspx). In that order, AFAIK if there's nothing set it takes the collation from the next level. – OMG Ponies Feb 08 '11 at 23:17
  • 3
    @dportas - The OP hasn't even mentioned columns in the question. They might equally well be talking about `IF 'Z' = 'z' PRINT 'Yes'` – Martin Smith Feb 09 '11 at 00:05
  • @Martin, true. IF there are no columns involved then case-sensitivity is determined by the database and/or server collation. As a general rule however, the database collation is not the determining factor. Every column has a collation and it is always the column collation, not the database collation that determines the comparison rules for a column. – nvogel Feb 09 '11 at 06:18
12

Case sensitivity depends only on the collation. You can specify the collation within each '=' operation

SELECT  *
  FROM  [Table_1] a inner join
        [Table_2] b on a.Col1=b.Col2 collate Modern_Spanish_CS_AI
pcofre
  • 3,976
  • 18
  • 27
5

I have been using the lower function to ensure the text comparison is insensitive till now. Is it still a good idea to follow the same?

Absolutely not. You will generally preclude the use of an index if you do this. Plain old = (or < or > or whatever) will either work or not depending on the collating you have chosen. Do not do this "just to be safe". Testing will make sure you've got it right.

lll
  • 297
  • 1
  • 3
3

The case sensitivity of operations in SQL Server is determined during installation when you set the collation for the database. At that point in time you can choose to install SQL Server as case insensitive (default) or case sensitive.

http://msdn.microsoft.com/en-us/library/aa197951(v=sql.80).aspx

Jake Hall
  • 1,963
  • 22
  • 24
  • In Microsoft SQL Server case-sensitivity is determined by the collation of the column(s) concerned and not by the collation of the database itself. – nvogel Feb 08 '11 at 23:14
1

How the comparison is done depends on the collation that you have chosen for the field. If you change the field to use a case sensetive collation, the comparisons will be case sensetive.

By default fields use the collation set for the database, but each field can have it's own collation setting.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005