All geek questions in one place

UPPER () and LOWER () are not required?

For a while, I thought that in order to correctly calculate the WHERE criteria, I need to consider case sensitivity. I would use UPPER() and LOWER() when it didn't matter. However, I find that the queries below give 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 the use of case-sensitive collisions. Is this the only way to force case sensitivity? Also, if you had case-insensitive matching, if UPPER() and LOWER() were needed?

Thanks for the help.

+4
source share
2 answers

A common SQL Server default for case insensitive sorting means that UPPER() and LOWER() not required when comparing strings.

In fact, an expression like

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

also can not be compared. I will not use the available indexes because of the function applied to the part column on the left side of the comparison.

+5
source

this query below gives a CASE SENSITIVE search:

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

UPPER() and LOWER() are only functions for changing the case of a letter, so if you are not case sensitive, they are used only after SELECT Keyword:

 SELECT UPPER('qwerty'), LOWER('Dog') returns QWERTY, dog
0
source

Source: https://habr.com/ru/post/1394533/

More articles:


All Articles