CODEWITHSUNDEEP
sqloracle11g
Juan Andres Mendez
Juan Andres Mendez

Reputation: 41

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.

Upvotes: 0

Views: 162

Answers (2)

user18098820
user18098820

Reputation:

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' ) );

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191455

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

Upvotes: 2

Related Questions