Is there a way to ignore and accept collate?

Question

I have a problem with a SQL request :

SELECT  CLCLEUNIK, NOM, VILLE, CODEP 
FROM CLIENT 
WHERE NOM LIKE :nom COLLATE French_CI_AI

In my case, I use the collate because a user can search "rhone" and it will find "rhône". But my problem is that now when someone searches "Rhône", the request doesn't find any match in my database.

Edit : this is the creation of the base :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CLIENT](


    [NOM] [varchar](50) NULL,
    [ADRESSE] [text] NULL,
    [CODEP] [varchar](5) NULL,
    [VILLE] [varchar](40) NULL,
    [TYPECLI] [int] NULL,
    [TEL] [varchar](20) NULL,



) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

This is the php code :

 public function searchCli(Request $request){
        require __DIR__.DIRECTORY_SEPARATOR."..".DIRECTORY_SEPARATOR."Entity".DIRECTORY_SEPARATOR."sqlconnect.php";


          $nom = $request->request->has("nom") ? $request->request->get("nom") : "";
          $nom = str_replace(" ", "%", $nom);


          $req ="SELECT  CLCLEUNIK, NOM, VILLE, CODEP FROM CLIENT WHERE NOM LIKE :nom COLLATE French_100_CI_AI";
          $prepare = $pdo->prepare($req);
          $prepare->execute(
            [
              "nom" => "%{$nom}%"
            ]
          );



          $clientsrecherche = $prepare->fetchAll(PDO::FETCH_OBJ);

          foreach($clientsrecherche as $key => $objet){
            foreach($objet as $key2 => $obj){
              $obj=utf8_encode($obj);
              $response[$key][$key2] = $obj;
            };
          }                     
            return new JsonResponse([ "response" => $response]);
       }

It's all I can have to help

You are using a case insensitive collation, so this should work... Can you please post a minimal reproducible example? — GMB, Jan 24 '20 at 10:14
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f37bb02155364df8a7974e6413310d89 — jarlh, Jan 24 '20 at 10:17
@NeoKerd post a *reproducible* example - CREATE TABLE statement, test data, query. It's quite possible the problem is because you use `varchar` strings, not the collation. If you use `varchar` with the wrong codepage, the *parameter values* themselves may already be garbled before they reach the server — Panagiotis Kanavos, Jan 24 '20 at 10:28
@PanagiotisKanavos I'm sorry I can't because I haven't create the database, I really don't know how to reproduce it — NeoKerd, Jan 24 '20 at 10:30
The very fact you had to use `COLLATE French_CI_AI` is a strong smell and a problem no matter what. Even without conversion errors, that statement prevents the server from using any indexes. Collation literally means the sorting order. Indexes depend on that sorting order to decide what values are equivalent, which goes before which one. If you have to use a collation different from the index, the index becomes unusable — Panagiotis Kanavos, Jan 24 '20 at 10:31
@NeoKerd how do you know you have a problem then? Somehow, you run one query and get an unexpected results. This means you already know what the *expected* and *actual* results are. You should be able to see the table's schema simply by opening SSMS or Azure Data Studio (essentially VS Code for databases). Right click on the table and select `Script` to generate the creation script — Panagiotis Kanavos, Jan 24 '20 at 10:33
@NeoKerd at the very least, you should check whether the field is `nvarchar` or `varchar`. If it's `varchar`, you have a deeper problem. In *any* case though PHP should be using `nvarchar` parameters, not `varchar`. What does your PHP code look like? — Panagiotis Kanavos, Jan 24 '20 at 10:35
@PanagiotisKanavos ok thank's I will Edit my post with few more informations — NeoKerd, Jan 24 '20 at 10:36
@NeoKerd the reason this matters is that `varchar` means text with codepage. Outside the US-ASCII range, the same byte values can represent different characters in different codepages. The server will try to translate text from the client codepage to the column's codepage. If the *client* codepage is wrong to begin with though, you'll end up with garbled text. That's what happens when you see "weird" text when opening an ASCII file, or `??` characters when an accented character was expected — Panagiotis Kanavos, Jan 24 '20 at 10:38
`varchar` .... don't use `varchar`. It's not just the collation, at some point someone is going to add a Dutch or Norwegian name. — Panagiotis Kanavos, Jan 24 '20 at 10:40
@PanagiotisKanavos I can't change the settings because is not my database ... You think I can't solve my problem so ? — NeoKerd, Jan 24 '20 at 10:42
@NeoKerd the problem *is* `varchar` to begin with. You'd have the exact same problem if you used MySQL and the database used one collation while the *client* used another. Or if the database used Latin1 while the client used UTF8 - in UTF8 non-English characters take 2 bytes at least. When you specify `French_` as the collation, you're asking for a single-byte codepage. — Panagiotis Kanavos, Jan 24 '20 at 10:44
@PanagiotisKanavos It's strange that it don't work for me because, I have to reproduce one application of my enterprise for mobile, so I have check the request in the application, and it's working fine — NeoKerd, Jan 24 '20 at 10:51
@NeoKerd to get this to work, server and client must use the same codepage. The column doesn't have an explicit COLLATE clause, so it uses the *database's* collation. Depending on the driver you use, you may be able to specify the codepage as a `charset` parameter in the connection string. — Panagiotis Kanavos, Jan 24 '20 at 10:54
@NeoKerd are you *sure* your driver allows named parameters? [The Microsoft PHP driver examples](https://learn.microsoft.com/en-us/sql/connect/php/example-application-pdo-sqlsrv-driver?view=sql-server-ver15) use positional parameters only — Panagiotis Kanavos, Jan 24 '20 at 10:55
@PanagiotisKanavos my connection to database is : $pdo = new PDO('dblib:dbname=DDMDL;/////);$pdo->exec("SET NAMES 'UTF-8'"); — NeoKerd, Jan 24 '20 at 10:58
Some quick Googling suggests that the use of `$pdo->setAttribute(PDO::ATTR_DEFAULT_STR_PARAM, PDO::PARAM_STR_NATL);` might help, by making parameters passed as Unicode strings by default (which tends to be a good idea even if your database is using `VARCHAR`, to avoid cleint-side encoding issues). — Jeroen Mostert, Jan 24 '20 at 14:14
Then I'd consider hooking up a Profiler instance to see what query is actually produced and sent over to SQL Server (assuming you have a Windows machine available). It is always hard to tell what client frameworks actually produce when they issue parameterized queries. You should also verify your intended query actually works when the parameter is a constant, if possible both inside and outside PHP to again weed out possible encoding issues. — Jeroen Mostert, Jan 24 '20 at 14:36
@JeroenMostert The request work well inside the microsoft sql management studio. There is only a problem in the php driver I think. — NeoKerd, Jan 24 '20 at 14:41

score -1 · Answer 1 · edited Jun 20 '20 at 09:12

-1

For more description:

collation properties:

CI specifies case-insensitive (case insensitive comparisons so 'ABC' would equal 'abc')

CS specifies case-sensitive

AS specifies accent-sensitive (accent sensitive, so 'ü' does not equal 'u')

AI specifies accent-insensitive

You can try French_100_CI_AI instead of French_CI_AI

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 24 '20 at 10:24

Hassan Hosseini

410
1
4
20

Still don't work but thank's for description it's good to know – NeoKerd Jan 24 '20 at 10:26
1

@NeoKerd , Your well come,just you should use `French_100_CI_AI` or `French_CI_AI`, They'r cover all situations. so find your problem in PHP code – Hassan Hosseini Jan 24 '20 at 16:01

Is there a way to ignore and accept collate?

1 Answers1

For more description:

collation properties: