Menu
I would like to know if there is an easy way to compare two text values ignoring the accents and upper case. Im working with an Oracle database. I already searched for an answer but honestly I don't understand what they proposed in here Accent and case insensitive collation in Oracle with LIKE. I tried that and it didn't work for me. Basically all I want to do is two compare to text values like 'pepé' and 'pepe' and obtain true as answer.
is it possible to do it without the LIKE instruction?
Thank You!
506
To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc' . In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc' , 'ABC' , or 'aBc' .
Starting with Oracle Database 10g Release 2 you can use the initialization parameters NLS_COMP and NLS_SORT to render all string comparisons case insensitive. We need to set the NLS_COMP parameter to LINGUISTIC, which will tell the database to use NLS_SORT for string comparisons.
For binary strings ( BINARY , VARBINARY , BLOB ), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons are case-sensitive.
You can replace special characters using the Oracle REPLACE function. To replace both carriage return and new line characters, you must use nested REPLACE functions. If you want to replace a lot of special characters, using many nested REPLACE functions can get messy and could have performance impacts.
Oracle Setup:
CREATE TABLE TABLE_NAME ( value ) AS
SELECT 'pepé' FROM DUAL;
-- Not necessary to create an index but it can speed things up.
CREATE INDEX value_without_accent_idx
ON TABLE_NAME ( CONVERT( value, 'US7ASCII' ) );
Query:
SELECT *
FROM table_name
WHERE CONVERT( value, 'US7ASCII' ) = 'pepe';
Output:
VALUE
-----
pepé
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
