Is SQL syntax case sensitive

Question

Is SQL case sensitive   I ve used MySQL and SQL Server which both seem to be case in-sensitive   Is this always the case   Does the standard define case-sensitivity

User · Answer

My understanding is that the SQL standard calls for case-insensitivity. I don't believe any databases follow the standard completely, though.

MySQL has a configuration setting as part of its "strict mode" (a grab bag of several settings that make MySQL more standards-compliant) for case sensitive or insensitive table names. Regardless of this setting, column names are still case-insensitive, although I think it affects how the column-names are displayed. I believe this setting is instance-wide, across all databases within the RDBMS instance, although I'm researching today to confirm this (and hoping the answer is no).

I like how Oracle handles this far better. In straight SQL, identifiers like table and column names are case insensitive. However, if for some reason you really desire to get explicit casing, you can enclose the identifier in double-quotes (which are quite different in Oracle SQL from the single-quotes used to enclose string data). So:

SELECT fieldName
FROM tableName;

will query fieldname from tablename, but

SELECT "fieldName"
FROM "tableName";

will query fieldName from tableName.

I'm pretty sure you could even use this mechanism to insert spaces or other non-standard characters into an identifier.

In this situation if for some reason you found explicitly-cased table and column names desirable it was available to you, but it was still something I would highly caution against.

My convention when I used Oracle on a daily basis was that in code I would put all Oracle SQL keywords in uppercase and all identifiers in lowercase. In documentation I would put all table and column names in uppercase. It was very convenient and readable to be able to do this (although sometimes a pain to type so many capitals in code -- I'm sure I could've found an editor feature to help, here).

In my opinion MySQL is particularly bad for differing about this on different platforms. We need to be able to dump databases on Windows and load them into UNIX, and doing so is a disaster if the installer on Windows forgot to put the RDBMS into case-sensitive mode. (To be fair, part of the reason this is a disaster is our coders made the bad decision, long ago, to rely on the case-sensitivity of MySQL on UNIX.) The people who wrote the Windows MySQL installer made it really convenient and Windows-like, and it was great to move toward giving people a checkbox to say "Would you like to turn on strict mode and make MySQL more standards-compliant?" But it is very convenient for MySQL to differ so signficantly from the standard, and then make matters worse by turning around and differing from its own de facto standard on different platforms. I'm sure that on differing Linux distributions this may be further compounded, as packagers for different distros probably have at times incorporated their own preferred MySQL configuration settings.

Here's another SO question that gets into discussing if case-sensitivity is desirable in an RDBMS.

User · Answer

Have the best of both worlds  These days you can just write all your sql statements in lowercase and if you ever need to have it formatted then just install a plugin that will do it for you   This is only applicable if your code editor has those plugins available   VSCode has many extensions that can do this

User · Answer

I don t think SQL Server is case-sensitive  at least not by default   When I m querying manually via Management Studio  I mess up case all the time and it cheerfully accepts it   select cOL1  col2 FrOM taBLeName WheRE

User · Answer

No  MySQL is not case sensitive  and neither is the SQL standard  It s just common practice to write the commands upper-case   Now  if you are talking about table column names  then yes they are  but not the commands themselves   So  SELECT   FROM foo    is the same as  select   from foo    but not the same as  select   from FOO

User · Answer

My understanding is that the SQL standard calls for case-insensitivity. I don't believe any databases follow the standard completely, though.

MySQL has a configuration setting as part of its "strict mode" (a grab bag of several settings that make MySQL more standards-compliant) for case sensitive or insensitive table names. Regardless of this setting, column names are still case-insensitive, although I think it affects how the column-names are displayed. I believe this setting is instance-wide, across all databases within the RDBMS instance, although I'm researching today to confirm this (and hoping the answer is no).

I like how Oracle handles this far better. In straight SQL, identifiers like table and column names are case insensitive. However, if for some reason you really desire to get explicit casing, you can enclose the identifier in double-quotes (which are quite different in Oracle SQL from the single-quotes used to enclose string data). So:

SELECT fieldName
FROM tableName;

will query fieldname from tablename, but

SELECT "fieldName"
FROM "tableName";

will query fieldName from tableName.

I'm pretty sure you could even use this mechanism to insert spaces or other non-standard characters into an identifier.

In this situation if for some reason you found explicitly-cased table and column names desirable it was available to you, but it was still something I would highly caution against.

My convention when I used Oracle on a daily basis was that in code I would put all Oracle SQL keywords in uppercase and all identifiers in lowercase. In documentation I would put all table and column names in uppercase. It was very convenient and readable to be able to do this (although sometimes a pain to type so many capitals in code -- I'm sure I could've found an editor feature to help, here).

In my opinion MySQL is particularly bad for differing about this on different platforms. We need to be able to dump databases on Windows and load them into UNIX, and doing so is a disaster if the installer on Windows forgot to put the RDBMS into case-sensitive mode. (To be fair, part of the reason this is a disaster is our coders made the bad decision, long ago, to rely on the case-sensitivity of MySQL on UNIX.) The people who wrote the Windows MySQL installer made it really convenient and Windows-like, and it was great to move toward giving people a checkbox to say "Would you like to turn on strict mode and make MySQL more standards-compliant?" But it is very convenient for MySQL to differ so signficantly from the standard, and then make matters worse by turning around and differing from its own de facto standard on different platforms. I'm sure that on differing Linux distributions this may be further compounded, as packagers for different distros probably have at times incorporated their own preferred MySQL configuration settings.

Here's another SO question that gets into discussing if case-sensitivity is desirable in an RDBMS.

User · Answer

SQL keywords are case insensitive themselves   Names of tables  columns etc  have a case sensitivity which is database dependent - you should probably assume that they are case sensitive unless you know otherwise  In many databases they aren t though  in MySQL table names are SOMETIMES case sensitive but most other names are not    Comparing data using        lt  etc  has a case awareness which is dependent on the collation settings which are in use on the individual database  table or even column in question  It s normal however  to keep collation fairly consistent within a database  We have a few columns which need to store case-sensitive values  they have a collation specifically set

User · Answer

SQL keywords are case insensitive themselves   Names of tables  columns etc  have a case sensitivity which is database dependent - you should probably assume that they are case sensitive unless you know otherwise  In many databases they aren t though  in MySQL table names are SOMETIMES case sensitive but most other names are not    Comparing data using        lt  etc  has a case awareness which is dependent on the collation settings which are in use on the individual database  table or even column in question  It s normal however  to keep collation fairly consistent within a database  We have a few columns which need to store case-sensitive values  they have a collation specifically set

User · Answer

The SQL Keywords are case-insensitive  SELECT  FROM  WHERE  etc   but are often written in all caps  However in some setups table and column names are case-sensitive  MySQL has a configuration option to enable disable it  Usually case-sensitive table and column names are the default on Linux MySQL and case-insensitive used to be the default on Windows  but now the installer asked about this during setup  For MSSQL it is a function of the database s collation setting   Here is the MySQL page about name case-sensitivity  Here is the article in MSDN about collations for MSSQL

User · Answer

This isn t strictly SQL language  but in SQL Server if your database collation is case-sensitive  then all table names are case-sensitive

User · Answer

Identifiers and reserved words should not be case sensitive  although many follow a convention to use capitals for reserved words and Pascal case for identifiers   See SQL-92 Sec  5 2

User · Answer

In Sql Server it is an option   Turning it on sucks   I m not sure about MySql

User · Answer

I found this blog post to be very helpful  I am not the author   Summarizing  please read  though          delimited identifiers are case sensitive   table name      Table Name    while non quoted identifiers are not  and are transformed to upper case  table name    TABLE NAME     He found DB2  Oracle and Interbase Firebird are 100  compliant      PostgreSQL     lowercases every unquoted identifier  instead of uppercasing it  MySQL     file system dependent  SQLite and SQL Server     case of the table and field names are preserved on creation  but they are completely ignored afterwards

User · Answer

The SQL92 specification states that identifiers might be quoted  or unquoted  If both sides are unquoted then they are always case-insensitive  e g  table name    TAble nAmE   However quoted identifiers are case-sensitive  e g   table name      TAble naME   Also based on the spec if you wish to compare unqouted identifiers with quoted ones  then unquoted and quoted identifiers can be considered the same  if the unquoted characters are uppercased  e g  TABLE NAME     TABLE NAME   but TABLE NAME     table name  or TABLE NAME     TAble NaMe     Here is the relevant part of the spec  section 5 2 13         13 A  lt regular identifier gt  and a  lt delimited identifier gt  are equiva-         lent if the  lt identifier body gt  of the  lt regular identifier gt   with         every letter that is a lower-case letter replaced by the equiva-         lent upper-case letter or letters  and the  lt delimited identifier         body gt  of the  lt delimited identifier gt   with all occurrences of          lt quote gt  replaced by  lt quote symbol gt  and all occurrences of  lt dou-         blequote symbol gt  replaced by  lt double quote gt    considered as         the repetition of a  lt character string literal gt  that specifies a          lt character set specification gt  of SQL TEXT and an implementation-         defined collation that is sensitive to case  compare equally         according to the comparison rules in Subclause 8 2    lt comparison         predicate gt      Note  that just like with other parts of the SQL standard  not all databases follow this section fully  PostgreSQL for example stores all unquoted identifiers lowercased instead of uppercased  so table name     table name   which is exactly the opposite of the standard   Also some databases are case-insensitive all the time  or case-sensitiveness depend on some setting in the DB or are dependent on some of the properties of the system  usually whether the filesystem is case-sensitive or not   Note that some database tools might send identifiers quoted all the time  so in instances where you mix queries generated by some tool  like a CREATE TABLE query generated by Liquibase or other DB migration tool   with hand made queries  like a simple JDBC select in your application  you have to make sure that the cases are consistent  especially on databases where quoted and unquoted identifiers are different  DB2  PostgreSQL  etc

[sql] Is SQL syntax case sensitive?

Examples related to sql

Examples related to case-sensitive