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.

CodePudding user response：

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

CodePudding user response：

You need to convert it to a coding without accents.
NB you will need to convert it wherever it is used in the SELECT otherwise you will get an error.

USE GROUP BY CONVERT( City, 'US7ASCII' ) 

NB If this is a large table and/or you will be querying it this way often you should consider creating an index to speed up the query.

CREATE INDEX index_city_no_accents ON table_name ( CONVERT( City, 'US7ASCII' ) );