Search code examples
sqloracle11g

Is there a way to GROUP BY accent insensitive in ORACLE?

I'm trying to perform a GROUP BY statement in ORACLE DBMS, when I found that there are some variables that are accent sensitive like CITY.

For example, lets say that I want to make the following SQL request:

SELECT city, COUNT(*)
FROM restaurant
WHERE pricing='High'
GROUP BY city;

Then my output would be

-----------------
|CITY  |COUNT(*)|
-----------------
|Bogota|      10|
-----------------
|Bogotá|       5|
-----------------

When the output I want is :

-----------------
|CITY  |COUNT(*)|
-----------------
|Bogota|      15|
-----------------

I want to know if there is a way to make the GROUP BY statement accent insensitive with out modifying the tables.

Solution

  • If you are on a recent version you can use COLLATE:

    SELECT city COLLATE UCA0700_DUCET_AI as city, COUNT(*)
FROM restaurant
WHERE pricing='High'
GROUP BY city COLLATE UCA0700_DUCET_AI;

    CITY    COUNT(*)
------  --------
Bogota  15

    This will also cover different case, as well as accents.

    db<>fiddle