I want to query records using SOQL in apex based on a string i extract from the file name the user has selected. The use case is to find all the contracts which has PrimaryKey__c = fileName . Here fileName is the name of the file selected by the user (we have a LWC component which gets the file name passes it to apex).
The SOQL is as simple as SELECT Id FROM Contract WHERE PrimaryKey =: str
The issue happens when the filename has unicode characters with accent. Example: Ö
The end user who uses the custom built LWC for file uploads uses MAC. This is important to note because OS X's HFS+ filesystem requires that all filenames be stored in the UTF-8 representation of their fully decomposed form.
Certain characters can be represented in two different forms in Unicode, for example, Ü can be represented as the single character Ü (known as “precomposed form” or NFC) or as two characters U and ¨ (known as “decomposed form” or NFD). Both are valid representations of the same string.In an OS X's HFS+ filename, Ö MUST be saved as O¨. whereas Linux and Windows usually use NFC.
Since apex receives file name in NFD format (when used from MAC)and the contract record has value stored in NFC format, the SOQL query returns no results.
How do I use conversions in apex to match both strings correctly? So that the both windows/Linux and MAC users can use this feature.
I read about removing the accents in Apex Eliminating Unicode accents in Apex
But I think, this solution will match value having Ü with Ü or U. Thus returning wrong records.
More info about the NFD and NFC formats: https://stackoverflow.com/questions/6153345/different-utf8-encoding-in-filenames-os-x and https://www.git-tower.com/help/guides/faq-and-tips/faq/unicode-filenames/mac
fileNameto NFC and NFD on JS side. Pass it to apex and write a SOQL to compare both NFC and NFD formats because we can not be sure what format thePrimaryKey__cwill have.