4

For a while I thought, in order for the WHERE criteria to be evaluated correctly, I need to account for case sensitivity. I would use UPPER() and LOWER() when case didn't matter. However, I am finding the below queries produce the same result. 

SELECT * FROM ATable WHERE UPPER(part) = 'SOMEPARTNAME'
SELECT * FROM ATable WHERE part        = 'SOMEPARTNAME'
SELECT * FROM ATable WHERE part        = 'somepartname'

SQL Case Sensitive String Compare explains to use case-sensitive collations. Is this the only way to force case sensitivity? Also, if you had a case-insensitive collation when would UPPER() and LOWER() be necessary?

Thanks for help.

Community
  • 1
  • 1
JSuar
  • 21,056
  • 4
  • 39
  • 83

2 Answers2

6

The common SQL Server default of a case-insensitive collation means that UPPER() and LOWER() are not required when comparing strings.

In fact an expression such as 

SELECT * FROM Table WHERE UPPER(part) = 'SOMEPARTNAME'

is also non-sargable i.e won't use available indexes, due to the function applied to the part column on the left hand side of the comparison.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
0

this query below produces CASE SENSITIVE search:

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

UPPER() and LOWER() are only functions to change the case of the letter so if you case-insensitive collation, they are only use after the SELECT Keyword:

SELECT UPPER('qwerty'), LOWER('Dog')

returns
QWERTY, dog
John Woo
  • 258,903
  • 69
  • 498
  • 492