Microsoft Query and SQL: non-case sensitive query

[ARAGORN II]

Hi all!

I hope to have chosen the right section of the forum, if not, I'm sorry, feel free to move my thread in the most appropriate one.

I'm writing since I have some difficulties in writing the correct SQL instructions when using Microsoft Query to pull out data from an Excel table. I'm using some parameters to retrive the desired records, my problem is that I cannot write the correct SQL instructions in order to perform a non-case sensitive and non-accent/special characters sensitive search.

Below you can see the data I'm working on.

In the red box there are the parameters I'm using to filter the records. Everything is working pretty well, the issue is when I try to perform a search entering a value for the "school name" parameter. Since the research performed is case sensitive and accent sensitive, I'm not able to get the result I'd like to have.

To be clearer, I'll make an example. Let's say I'd like to retrive all the records containing "Republique" OR "REPUBLIQUE" OR "République" OR "republique" in the school name, could you suggest me how I should edit the SQL instructions to make this happen?

This is the SQL code I have:

I tried using COLLATE UTF8_GENERAL_CI before LIKE but I'm not sure if I'm having the right approach.

Thanks.

Have a nice evening,
Stefano

 

[Kyle123]

Which database are you using? Most SQL databases are case insensitive and in some cases one must jump through a considerable amount of hoops to make them case sensitive.

So you'd normally write something like:

SELECT * FROM someTable WHERE somefield LIKE '%republic%'

This would return all records matching:
republic
Republic
REPUBLIC
RePuBLic
...

You get the idea.

It would not return Républic since é is not the same character as e

 

[ARAGORN II]

Hi Kyle, thank you very much for your reply.

For some reason I cannot see the pictures I attached in my first message, I'll try to re-attach them here, hoping it will work.

I'm sorry, I'm not an expert at all about databases. I'm using Microsoft Query directly from Excel (Data -> From other sources -> From Microsoft Query). Is there any additional information you'd like to know?

Also thank you for your advice, in this case my query returns only the records matching "republic".

Actually, the issue I'm facing is due to the fact I'm using parameters defined in Excel cells to filter the records.

Going a bit into details, now I'm using the instruction:

SELECT * FROM someTable WHERE somefield LIKE '%' & ? & '%'

where ? is the parameter assigned to cell E6 ("school name") in the Excel worksheet above. In this way I can write whatever I want in that cell to filter the records accordingly. Also having '%' before and after ? allows me to return all the records if I leave cell E6 blank.

The fact is that if I try to write in E6 "publique" I get 4 records but if I write "Republique" I get 0 records while I'd like to get 4 (I won't be the one using this database).

Looking at this discussion, they suggest to use COLLATE to achieve what I was trying to do but I tried in several ways and I don't really understand how I should modify my SQL code.

Thank you very much,
Stefano

 

[Kyle123]

This isn't a case sensitivity issue,

Republique <> République

The diacritic (é) makes them completely different words, like won't work here

 

[ARAGORN II]

Yes, you're right but my problem is also with "République" and "république".

In the first case I get 3 records, in the second case only 1 (considering the 11 records shown in the image attached above).