Confused about default string comparison option in SQL Server

Question

I am completely confused about the default string comparison method used in Microsoft SQL Server. Up till now I had been using UPPER() and LOWER() functions for performing any string comparison on Microsoft SQL Server.

However got to know that by default Microsoft SQL Server is case insensitive and we need to change the collation while installing Microsoft SQL Server to make it case sensitive. However if this is the case then what is the use of UPPER and LOWER() functions.

I use them for formatting text into upper case or lower case or sometimes I get funky and do title case. — T I, Aug 21 '13 at 09:33
possible duplicate of [SQL Case Sensitive String Compare](http://stackoverflow.com/questions/3969059/sql-case-sensitive-string-compare) — Tanner, Aug 21 '13 at 09:34
What makes you think that *comparisons* are the only use case for such functions (in this or any language)? — Damien_The_Unbeliever, Aug 21 '13 at 10:11

podiluska · Answer 1 · 2013-08-21T09:41:27.723

As you have discovered, upper and lower are only of use in comparisons when you have a case-sensitive collation applied, but that doesn't make them useless.

For example, Upper and Lower can be used for formatting results.

select upper(LicencePlate) from cars

You can apply collations without reinstalling, by applying to a column in the table design, or to specific comparisons ie:

if 'a' = 'A' collate latin1_general_cs_as
    select '1'
else
    select '2'

if 'a' = 'A' collate latin1_general_ci_as
    select '3'
else
    select '4'

See http://technet.microsoft.com/en-us/library/aa258272(v=sql.80).aspx

score 1 · Answer 2 · answered Aug 21 '13 at 09:39

1

if you like to compare case sensitive string this might be the syntax you looking for

IF @STR1 COLLATE Latin1_General_CS_AS <> @STR2 COLLATE Latin1_General_CS_AS
    PRINT 'NOT MATCH'

answered Aug 21 '13 at 09:39

tsohtan

830
1
13
33

Confused about default string comparison option in SQL Server

2 Answers2