How to compare strings in sql ignoring case?

How do I write a query in Oracle ignoring the case of the strings being compared? For example "angel", "Angel", "ANGEL", "angel", "AngEl" would all be equal when compared.

Solution 1:

If you are matching the full value of the field use

WHERE UPPER(fieldName) = 'ANGEL'

EDIT: From your comment you want to use:

SELECT 
    RPAD(a.name, 10,'=') "Nombre del Cliente"
    , RPAD(b.name, 12,'*') "Nombre del Consumidor" 
FROM 
    s_customer a, 
    s_region b 
WHERE 
    a.region_id = b.id 
    AND UPPER(a.name) LIKE '%SPORT%' 

Solution 2:

You could use the UPPER keyword:

SELECT *
FROM Customers
WHERE UPPER(LastName) = UPPER('AnGel')

Solution 3:

You can use:

select * from your_table where upper(your_column) like '%ANGEL%'

Otherwise, you can use:

select * from your_table where upper(your_column) = 'ANGEL'

Which will be more efficient if you are looking for a match with no additional characters before or after your_column field as Gary Ray suggested in his comments.

Solution 4:

before comparing the two or more strings first execute the following commands

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

after those two statements executed then you may compare the strings and there will be case insensitive.for example you had two strings s1='Apple' and s2='apple', if yow want to compare the two strings before executing the above statements then those two strings will be treated as two different strings but when you compare the strings after the execution of the two alter statements then those two strings s1 and s2 will be treated as the same string

reasons for using those two statements

We need to set NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI in order to use 10gR2 case insensitivity. Since these are session modifiable, it is not as simple as setting them in the initialization parameters. We can set them in the initialization parameters but they then only affect the server and not the client side.