Accent and case insensitive COLLATE equivalent in Oracle

Question

In Microsoft SQL Server, if I want to search case insensitively in a case-sensitive database, I can run the following SQL:

SELECT * FROM MyTable
WHERE MyField = 'BobDillon' COLLATE Latin1_General_CI_AI

And that will find all "bobdillon" entries.

If I want to do the same in Oracle, I know I can do this:

SELECT * FROM MyTable
WHERE UPPER(MyField) = 'BOBDILLON'

But I want to know if there is a direct equivalent to the collate keyword, so I can search for case sensitivity and accent sensitivity as I see fit.

Answer 1

SELECT *
FROM MyTable
WHERE NLSSORT(MyField, 'NLS_SORT = Latin_AI') = NLSSORT('BobDillon', 'NLS_SORT = Latin_AI')