is SQL Case Sensitive

Is SQL Case Sensitive

Before discussing whether or not SQL is case-sensitive or not let's understand what case-sensitive means. When we differentiate a word or letter in upper or lower case is called case sensitivity and when the words are treated equally is called case insensitive.

It is a very complicated topic, whether SQL is case-sensitive or not. The case sensitive differs from one database to another database as some are case sensitive and some are case insensitive.

If we consider the keyword in SQL like (SELECT, WHERE, and FROM) these are insensitive in all the DBMS. No matter how you write them they will function. And if we consider different parts like tables and column names, values in columns may be sensitive or insensitive in different DBMS.

Let's see whether the columns and table are case sensitive or not.

Are SQL Table and Column Names Case-Sensitive?

The case-sensitivity depends on different DBMS. Some Operating systems such as Linux, Windows, or *macOS * also impact the behavior of the DBMS system.

Let's discuss a few DBMS systems and try to understand how case sensitivity works in different DBMS.

Let's look into some of the relational databases:

MYSQL.

In MYSQL, table and column names are not case sensitive on windows as MYSQL represents databases as a file and directories on the server. As a result of this case sensitivity of databases depends upon how the operating system works. But they are case sensitive on Unix and Linux-based operating systems as tables are case sensitive in Unix. We can change behavior by changing collation in MySQL.

When we create the table, the case of the column name is preserved even when not quoted. In MYSQL the column name in the select statement is not case sensitive. In the following query as you can see the MYSQL returns the column name in a case that is created at the time of table creation. As you can see in the following create table query, the name of the column is a fact. The uppercase and lowercase alphabets are treated equally i.e columns are case insensitive in MYSQL

So, the following query returns the same output.

The name of the column is preserved even if it is quoted.

PostgreSQL

By default PostgreSQL database is case-sensitive. In these databases, table and column names are case sensitive and their behavior doesn't depend on the operating system. In PostgreSQL, column names are not preserved unless the column names are quoted. The column names in PostgreSQL are not case sensitive unless quoted.

MSSQL

MSSQL is case-insensitive like MYSQL. In MSSQL, table and column names are not case sensitive on windows as MSSQL represents databases as a file and directories on the server. As a result of this case sensitivity of databases depends upon how the operating system works. But they are case sensitive on Unix and Linux-based operating systems as tables are case sensitive in Unix. We can change behavior by changing collation in MSSQL.

Are Column Value in Name Case Sensitive?

Let's see whether the column values are case-sensitive in SQL. It also depends on the DBMS. String comparison is case sensitive in PostgreSQL for example WHERE column_name = 'pat' returns the value exactly equal to the pat. The same works with the like operator Column like 'Pat' or column like 'pAt' will provide the false result. If we want to use the case insensitive comparison use the ILIKE operator for example Column like 'Pat' or column like 'pAt' returns the true result for 'pat'.

MySQL and MSSQL have the case insensitive behavior by default i.e where column = PATreturns true for 'pat', 'PAT', 'pAt'. The same works for the like operator in MSSQL and MYSQL. We can change the collation setting to modify the default behavior.

How to make SQL Like Case Sensitive

In PostgreSQL, we use a LIKE operator to make a case sensitive. let's see what is LIKE and how it is used to get sensitive data.LIKE operator is used for data matching patterns. The LIKE clause allows the comparison of one string pattern to another string pattern, which is not identical. the LIKE operator is more sensitive toward the pattern it fetches.

Let's see a few examples of the LIKE operator.

Table: Student

Now we will select the name i.e begins with the 'Sa' using the LIKE operator

Here we have written a query that will return a pattern matching with Sa. The output of the above query will be.

The LIKE operator will return the result starting with the 'Sa'. In such a sense we can use the LIKE operator for getting the case sensitive data.

In MYSQL LIKE is not case sensitive. So we force the LIKE to act as case sensitive. If we want to make an MYSQL LIKE a case sensitive we will cast the BINARY operator. And to do this we use bit-by-bit comparison. Before going ahead let's see how the BINARY works.