Menu
Can you ignore case in a group by? For example if there is a table of states but it has records with "Alabama" and "alabama", or "Alaska" and "alaska" and you want the group by that column but just get back a single 'group' for Alabama and Alaska.
thanks
781
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.
Another way for case-insensitive matching is to use a different “collation”. The default collations used by SQL Server and MySQL do not distinguish between upper and lower case letters—they are case-insensitive by default. The logic of this query is perfectly reasonable but the execution plan is not: DB2.
If the User Locale is set to 'English', the GROUP BY in the SOQL behaves as case insensitive but for other user locales, it behaves as case sensitive. This is working as designed behavior when using current database. Via Anil on twitter.
Select * from table where upper(table.name) like upper('IgNoreCaSe'); Alternatively, substitute lower for upper.
Just use UPPER:
select upper(state), count(1)
from your_table
group by upper(state);
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
