How do you perform a case-insensitive search in SQL?

Question

Aryan Kumar · Answer

Performing a case-sensitive search in SQL depends on the database system you are using. SQL databases often provide functions or collations that can be used to achieve case sensitivity. Here's a general approach and some examples for common database systems:

Using BINARY or COLLATE (General Approach):

One way to perform a case-sensitive search is to use the BINARY keyword or specify a case-sensitive collation in your query.

-- Using BINARY keyword
SELECT * FROM your_table WHERE BINARY column_name = 'CaseSensitiveValue';

-- Using a case-sensitive collation
SELECT * FROM your_table WHERE column_name COLLATE utf8_bin = 'CaseSensitiveValue';


SELECT * FROM your_table WHERE column_name COLLATE Latin1_General_CS_AS = 'CaseSensitiveValue';

Database-Specific Functions (PostgreSQL):

In PostgreSQL, you can use the COLLATE clause along with the pg_catalog.C collation to perform a case-sensitive search.

SELECT * FROM your_table WHERE column_name COLLATE "C" = 'CaseSensitiveValue';

Binary Collations (Oracle):

Oracle databases use binary collations for case-sensitive searches.

SELECT * FROM your_table WHERE column_name = 'CaseSensitiveValue' COLLATE BINARY;

Database Configuration (SQLite):

SQLite is case-sensitive by default, so you don't typically need additional syntax for case-sensitive searches.

SELECT * FROM your_table WHERE column_name = 'CaseSensitiveValue';

Please note that the exact syntax and options for performing case-sensitive searches may vary depending on the specific database system you are using. It's essential to consult your database documentation for the most accurate and database-specific approach.

forum

How do you perform a case-insensitive search in SQL?

Can you answer this question?

1 Answers