CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Vishakha
Vishakha

Reputation: 11

Confused about default string comparison option in SQL Server

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.

Upvotes: 0

Views: 510

Answers (2)

podiluska
podiluska

Reputation: 51494

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

Upvotes: 1

tsohtan
tsohtan

Reputation: 850

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'

Upvotes: 1

Related Questions