Sharepoint Query with CAML and Accents

1
votes

I am trying to build a Query with CAML and it seems it is working quite well, but I have a problema, I want the Caml to find the value even if it has accented words or not.

For example, now if I search for "Carlos" the caml will get me the list items that contains "carlos" or "CARLOS" or "CaRlOs" or any combination of Capital or small letters. Now I want it to get me too the ones with accented letters for example it shoud return tu "Cárlos" or "carlös" or "càrlos"... ¿is there an option?

The caml query I am using is quite simple, it looks in a Sharepoint list ...

carlos

Thanks for any comments !!

2
sharepoint-2010

2 Answers

1
votes

The CAML engine takes in that wretched "query language", translates the exposed names/value from the schema to the back-end format, builds the appropriate SQL query, and then sends the entire mess off to SQL Server.

The collation -- including insensitivity -- rules are part of SQL Server, and not SharePoint (or the CAML engine) itself (although there could be subtle bugs introduced; you have been warned!).

SQL Server supports different COLLATIONS (including some that are accent-insensitive as well as the "standard" case-insensitive). However, SharePoint runs within a rather limited "supported" configuration -- changing the collation may be inadvisable.

All of the databases required by SharePoint Server use the Latin1_General_CI_AS_KS_WS collation.

However, it may be possible to hack the appropriate SQL table backing the list with ALTER TABLE and specify an alternate collation such as SQL_Latin1_General_Cp1_CI_AI (AI = Accent-Insensitive, AS = Accent-Sensitive). Your mileage may vary: this is not a supported scenario.

Another option might be use an item trigger and code-behind to "normalize" all the values -- to, say, "carlos". (These normalized values would be stored in a different column.) The code-behind can use the full power of .NET for this step but does also introduce additional complications/requirements.

Happy coding.

1
votes

PST the tip you gave me worked perfectly, easy and fast ... this is the code I used to normalize the strings.

public static string Normalizarstring(this string cadena)
        {
            StringBuilder sb = new StringBuilder();

            cadena.Normalize(NormalizationForm.FormD).ToCharArray().ToList()
                .ForEach(caracter => sb.Append((CharUnicodeInfo.GetUnicodeCategory(caracter) != UnicodeCategory.NonSpacingMark) ? caracter.ToString() : ""));

            return (sb.ToString().Normalize(NormalizationForm.FormC));
        }

Thanks again !!!