Menu
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.
616
SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive.
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.
Oracle names aren't case sensitive. PostgreSQL names are case sensitive. By default, AWS SCT uses object name in lower-case for PostgreSQL. In most cases, you'll want to use AWS DMS transformations to change schema, table, and column names to lower case.
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' .
SELECT *
FROM MyTable
WHERE NLSSORT(MyField, 'NLS_SORT = Latin_AI') = NLSSORT('BobDillon', 'NLS_SORT = Latin_AI')
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
