question in sql

Question

Hello, how I can create a query in Oracle for "angel" no matter if it is Angel, ANGEL, angel AngEl

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

Answer 2

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.

Answer 3

You could use the UPPER keyword:

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

Answer 4

I don't recall the exact syntax, but you can actually 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.

Answer 5

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