How to compare strings in sql ignoring case

Question

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

User · Answer

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%'

User · Answer

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

User · Answer

I don t recall the exact syntax  but you may set the table column to be case insensitive  But be careful because then you won t be able to match based on case anymore and if you WANT  cool  to not match  CoOl  it will no longer be possible

User · Answer

To avoid string conversions comparisons  use COLLATE SQL Latin1 General CP1 CI AS  EXAMPLE  SELECT UserName FROM Users WHERE UserName   COLLATE SQL Latin1 General CP1 CI AS      Angel   That will return any usernames  whether ANGEL  angel  or Angel  etc

User · Answer

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

User · Answer

More detail on Mr Dredel s answer and tuinstoel s comment  The data in the column will be stored in its specific case  but you can change your session s case-sensitivity for matching   You can change either the session or the database to use linguistic or case insensitive searching  You can also set up indexes to use particular sort orders   eg  ALTER SESSION SET NLS SORT BINARY CI    Once you start getting into non-english languages  with accents and so on  there s additional support for accent-insensitive  Some of the capabilities vary by version  so check out the Globablization document for your particular version of Oracle  The latest  11g  is here

User · Answer

You could use the UPPER keyword   SELECT   FROM Customers WHERE UPPER LastName    UPPER  AnGel

User · Answer

SELECT STRCMP  quot string1 quot    quot string2 quot    this returns 0 if the strings are equal   If string1   string2  this function returns 0  ignoring the case  If string1  lt  string2  this function returns -1 If string1  gt  string2  this function returns 1  https   www w3schools com sql func mysql strcmp asp

[sql] How to compare strings in sql ignoring case?

Examples related to sql

Examples related to oracle