[sql] Case insensitive searching in Oracle

From Oracle 12c R2 you could use COLLATE operator:

The COLLATE operator determines the collation for an expression. This operator enables you to override the collation that the database would have derived for the expression using standard collation derivation rules.

The COLLATE operator takes one argument, collation_name, for which you can specify a named collation or pseudo-collation. If the collation name contains a space, then you must enclose the name in double quotation marks.

Demo:

CREATE TABLE tab1(i INT PRIMARY KEY, name VARCHAR2(100));

INSERT INTO tab1(i, name) VALUES (1, 'John');
INSERT INTO tab1(i, name) VALUES (2, 'Joe');
INSERT INTO tab1(i, name) VALUES (3, 'Billy'); 
--========================================================================--
SELECT /*csv*/ *
FROM tab1
WHERE name = 'jOHN' ;
-- no rows selected

SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI = 'jOHN' ;
/*
"I","NAME"
1,"John"
*/

SELECT /*csv*/ *
FROM tab1 
WHERE name LIKE 'j%';
-- no rows selected

SELECT /*csv*/ *
FROM tab1 
WHERE name COLLATE BINARY_CI LIKE 'j%';
/*
"I","NAME"
1,"John"
2,"Joe"
*/

db<>fiddle demo

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to case-sensitive

Are PostgreSQL column names case-sensitive? How can I convert uppercase letters to lowercase in Notepad++ How do I commit case-sensitive only filename changes in Git? In VBA get rid of the case sensitivity when comparing words? Changing capitalization of filenames in Git How to compare character ignoring case in primitive types Contains case insensitive Should URL be case sensitive? MySQL case sensitive query Are table names in MySQL case sensitive?

Examples related to case-insensitive

Are PostgreSQL column names case-sensitive? How to make String.Contains case insensitive? SQL- Ignore case while searching for a string String contains - ignore case How to compare character ignoring case in primitive types How to perform case-insensitive sorting in JavaScript? Contains case insensitive Case insensitive string as HashMap key Case insensitive searching in Oracle How to replace case-insensitive literal substrings in Java

Examples related to sql-like

SQL Server: use CASE with LIKE Create hive table using "as select" or "like" and also specify delimiter How do I find ' % ' with the LIKE operator in SQL Server? Using LIKE operator with stored procedure parameters SQL- Ignore case while searching for a string Is the LIKE operator case-sensitive with MSSQL Server? Using Eloquent ORM in Laravel to perform search of database using LIKE SQL 'LIKE' query using '%' where the search criteria contains '%' How to use "like" and "not like" in SQL MSAccess for the same field? MySQL SELECT LIKE or REGEXP to match multiple words in one record