What is the extent of Case sensitivity in SQL?

Hello! I just started learning SQL and I have a doubt

Is it CASE sensitive while selecting column names?
Also is it case sensitive while typing text characters?

Example:

name =‘David%’
Does the D here have to be capital?

Thank you

Hello @blog8455292830, welcome to the forums. Yes, the D is case sensitive. When writing syntax, the keywords, LIKE, etc are not case sensitive, but it is best practise to write them in capitals.
I hope this helps!

Generally speaking, one ought to match the case of the column name in the query. If your column name was defined as “FirstName” for example, you should aim to write it that way in your query.

Whether or not the DBMS will understand you even if you don’t match the case is irrelevant, really - we ought to be as precise as possible with our queries, not write haphazard SQL. :slight_smile:

That very much depends. SQL has what’s called “collation”, which specifies the character set in use for the database as well as rules which should be applied when comparing and evaluating the characters in that set.

For example, MySQL has a collation latin1_general_ci. What this tells us is that it uses the latin1 character set (i.e. the characters commonly used by the Western European languages), and that it is case-insensitive, so david would be treated as equivalent to David or DaViD.

There is another collation, latin1_general_cs, which uses the same character set but is case sensitive.

Again, generally speaking one should write the query to accurately reflect what you’re asking your database to return - so if you were expecting a record to have a value of David then that is what you ought to write. If you don’t get the expected results, then you might try a subtle difference in case (say david) and see if that changes things.

1 Like

To add on to this, in SQLite specifically, the keyword LIKE is not case sensitive for most characters. The GLOB keyword (not taught here on Codecademy) is case sensitive by default.

As @thepitycoder said, it is always best to be as accurate as possible to what you are asking for in return, but there are also differences depending on the DBMS you are using.

For more info on the specifics of SQLite LIKE and GLOB keywords, check out the documentation here (under section 5).

2 Likes