The case sensitivity of SQL depends on the collation settings of the database or individual columns. In many databases, the default collation is case-insensitive, meaning that string comparisons are not case-sensitive. However, you can explicitly specify a case-sensitive or case-insensitive collation for specific queries or columns.
Here’s a live example in SQL Server:
-- Create a sample table with a case-insensitive collationCREATE TABLE CaseInsensitiveExample ( ID INT PRIMARY KEY, Name NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS -- Case-insensitive collation);-- Insert data into the tableINSERT INTO CaseInsensitiveExample VALUES (1, 'John Doe');INSERT INTO CaseInsensitiveExample VALUES (2, 'Jane Smith');INSERT INTO CaseInsensitiveExample VALUES (3, 'Bob Johnson');-- Query with a case-insensitive comparisonSELECT *FROM CaseInsensitiveExampleWHERE Name = 'john doe'; -- Case-insensitive comparison-- Create a sample table with a case-sensitive collationCREATE TABLE CaseSensitiveExample ( ID INT PRIMARY KEY, Name NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS -- Case-sensitive collation);-- Insert data into the tableINSERT INTO CaseSensitiveExample VALUES (1, 'John Doe');INSERT INTO CaseSensitiveExample VALUES (2, 'Jane Smith');INSERT INTO CaseSensitiveExample VALUES (3, 'Bob Johnson');-- Query with a case-sensitive comparisonSELECT *FROM CaseSensitiveExampleWHERE Name = 'john doe'; -- Case-sensitive comparisonIn the above example:
- The ‘
CaseInsensitiveExample‘ table is created with a case-insensitive collation (‘SQL_Latin1_General_CP1_CI_AS‘) for the ‘Name‘ column. The subsequent query performs a case-insensitive comparison in the ‘WHERE‘ clause. - The ‘
CaseSensitiveExample‘ table is created with a case-sensitive collation (‘SQL_Latin1_General_CP1_CS_AS‘) for the ‘Name‘ column. The subsequent query performs a case-sensitive comparison in the ‘WHERE‘ clause.
Note that collation settings can vary between database systems. The above example uses SQL Server syntax, and collation names may differ in other database systems such as MySQL or PostgreSQL. Always refer to the documentation of the specific database system you are working with for accurate information on collations and case sensitivity.
