In SQL, is Comparison character = always absolute regarding Upper or Lower case?

In this babies table exercise,

Typing the code query below returns no query result because the name case (First letter) isn’t exactly as written in the table. But when changed to upper case ‘Lillian’ it returns results. Is this always the case? And how can it be written not to be absolute/case sensitive?

SELECT year, number
FROM babies
WHERE name = ‘lillian’ AND gender = ‘F’;

Short answer: yes queries are case sensitive. There is a way around it though. My SQL is a little rusty, but I found this link: https://alvinalexander.com/sql/sql-select-case-insensitive-query-queries-upper-lower

Using either upper() or lower() methods will allow you to find results regardless of case.
For example:

SELECT year, number
FROM babies
WHERE lower(name) = 'lillian' AND upper(gender) = 'F';

By converting the stored values to the same case as your search string, you eliminate differences in case. These conversions only apply in the query. They don’t permanently change the stored values.
Hope this helps!

1 Like