Comment traîter des chaînes de caractères UTF-8 ?

De nombreux articles évoquent le fait que SQL Server ne supporte pas l’encodage UTF-8 mais uniquement l’encodage UTF-16. Ce support a d’ailleurs été amélioré à partir de SQL Server 2012 avec de nouveaux classements (voir Prise en charge d’Unicode sous SQL Server).

Mais l’expérience nous montre que de nombreuses données que l’on cherche à traiter sont en UTF-8, et on n’a pas nécessairement envie de sortir la grosse artillerie (par exemple un import via SQL Server Integration Service). Je vous propose donc une manière simple d’importer correctement dans un script T-SQL un fichier encodé en UTF-8.

Tout d’abord, nous allons construire un petit fichier encodé en UTF-8. que nous chercherons ensuite à importer proprement. Voici ici le fichier qui nous servira de base (j’utilise Notepad++ pour bien fixer le fichier dans le bon format d’encodage).

Fichier_UTF8

Ce fichier est bien encodé en UTF-8, comme indiqué en bas à droite de l’image. Ce fichier présente notamment quelques accents, qui sont typiquement problématiques lorsqu’il est question d’encodages.

Pour lire un fichier et positionner son contenu dans une chaîne de caractères, une des possibilités est d’utiliser la commande OPENROWSET (voir l’aide en ligne ici). Mais on remarque notamment dans cette aide en ligne que cette commande, bien qu’acceptant un paramètre qui permet de préciser le code page du fichier d’entrée, ne supporte pas le format UTF-8. Le même souci se retrouve de manière générale au niveau de SQL Server. UTF-16 est bien supporté, mais pas UTF-8.

Au final, si on prend notre fichier et que l’on cherche à l’importer dans une chaîne de caractères, les accents ne passent pas l’épreuve avec succès, loin de là …

Lecture1

Etant donné que les fonctions intégrées à SQL Server ne permettent pas d’intégrer correctement le code page UTF-8, une solution consiste à passer par le développement d’une méthode spécifique qui utilise la CLR.

Plusieurs solutions sont possibles, je vous propose ici le code d’une fonction scalaire, qui prend en entrée un binaire (le contenu du fichier) et présente en sortie une chaine Unicode correctement convertie. Nous nous appuierons sur une version simplifiée du code présenté dans cet article du MSDN à propos de la définition d’un type de données utilisateur dédié aux chaînes UTF-8.

Dans un premier temps, nous devront activer la CLR intégrée au moteur SQL Server afin de pouvoir utiliser des fonctions développées en utilisant du code .Net.

sp_configure 'clr enabled', 1 
GO 
RECONFIGURE 
GO

Ensuite, il suffit de mettre en place deux petites fonctions utilisées pour la conversion.

 

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public sealed class UTF8Tools
{

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString ConversionBinaireUTF8VersNVarchar(SqlBinary entree)
    {
        SqlString sortie = (entree.IsNull) ? SqlString.Null : new SqlString(System.Text.Encoding.UTF8.GetString(entree.Value));
        return sortie;
    }

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBinary ConversionNVarcharVersBinaireUTF8(SqlString entree)
    {
        SqlBinary sortie = (entree.IsNull) ? SqlBinary.Null : new SqlBinary(System.Text.Encoding.UTF8.GetBytes(entree.ToString()));
        return sortie;
    }
}

Ce code permet d’avoir deux fonctions de conversion d’un contenu binaire de fichier en UTF-8 vers une chaîne de caractères et vice-versa.

Pour déployer ces fonctions vers le serveur, il est possible de compiler et publier via Visual Studio, ou de charger depuis SSMS une assemblée publiée. Une autre possibilité est de déployer directement le code binaire de l’assemblée.

 

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE ASSEMBLY [UTF8Tools]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103009B2338550000000000000000E00002210B010B00000C000000060000000000001E2A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000D02900004B000000004000000803000000000000000000000000000000000000006000000C000000982800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240A000000200000000C000000020000000000000000000000000000200000602E72737263000000080300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000002A0000000000004800000002000500CC200000CC07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330020029000000010000110F00281200000A2D18281300000A0F00281400000A6F1500000A731600000A2B057E1700000A0A062A000000133002002F000000020000110F00281800000A2D1E281300000A0F00FE16020000016F1900000A6F1A00000A731B00000A2B057E1C00000A0A062A1E02281D00000A2A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000074020000237E0000E00200006803000023537472696E67730000000048060000080000002355530050060000100000002347554944000000600600006C01000023426C6F620000000000000002000001471502000900000000FA25330016000001000000160000000200000003000000020000001D0000001000000002000000010000000200000000000A00010000000000060032002B000A005A0045000A00640045000600D900BF0006000401F20006001B01F20006003801F20006005701F20006007001F20006008901F2000600A401F2000600BF01F2000600F701D80106000B02D80106001902F20006003202F200060062024F024700760200000600A50285020600C50285020A00FE02E30206002A031E030000000001000000000001000100010110001800000005000100010050200000000096006E000A0001008820000000009600900011000200C320000000008618B2001800030000000100B80000000100B8002100B2001C002900B2001C003100B2001C003900B2001C004100B2001C004900B2001C005100B2001C005900B2001C006100B2001C006900B20021007100B2001C007900B2001C008100B2001C008900B20026009900B2002C00A100B2001800A900B2001800190013035600B10033035A0019003C035F00B100460364001100B2001C00110050036A00110013035600090055037300B1005E0377001900B2007D001900500383000900B200180020008B0031002E003300D6002E000B008C002E001300D6002E001B00E5002E002300E5002E002B00E5002E005B0003012E0083004C012E004300E5002E005300E5002E003B00EB002E006B002D012E0073003A012E007B00430140008B0031006E008700048000000100000000000000000000000000180000000400000000000000000000000100220000000000040000000000000000000000010039000000000000000000003C4D6F64756C653E0055544638546F6F6C732E646C6C0055544638546F6F6C73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C42696E61727900436F6E76657273696F6E42696E6169726555544638566572734E5661726368617200436F6E76657273696F6E4E566172636861725665727342696E6169726555544638002E63746F7200656E747265650053797374656D2E52756E74696D652E56657273696F6E696E67005461726765744672616D65776F726B4174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C0053797374656D2E5465787400456E636F64696E67006765745F55544638006765745F56616C756500476574537472696E67004E756C6C00546F537472696E670047657442797465730000000320000000000067D38BEA76C4D441A3EFF46221AEEA030008B77A5C561934E0890600011109110D060001110D110903200001042001010E04200101020520010111490420010108240100020054020F497344657465726D696E697374696301540209497350726563697365010320000204000012590420001D050520010E1D050306110904070111090320000E0520011D050E052001011D050306110D040701110D4901001A2E4E45544672616D65776F726B2C56657273696F6E3D76342E350100540E144672616D65776F726B446973706C61794E616D65122E4E4554204672616D65776F726B20342E350E01000955544638546F6F6C73000005010000000017010012436F7079726967687420C2A920203230313500002901002466343335653836312D666537642D343838372D613738622D62353966333465303461373700000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000009B23385500000000020000001C010000B4280000B40A0000525344536C5748640BC466408AE4C6383BF11C7C01000000633A5C55736572735C41646D696E6973747261746F725C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C55544638546F6F6C735C55544638546F6F6C735C6F626A5C52656C656173655C55544638546F6F6C732E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F829000000000000000000000E2A0000002000000000000000000000000000000000000000000000002A00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC01000001003000300030003000300034006200300000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000550054004600380054006F006F006C0073000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000550054004600380054006F006F006C0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003500000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000550054004600380054006F006F006C0073002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000550054004600380054006F006F006C0073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[ConversionBinaireUTF8VersNVarchar](@contenu [varbinary](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS 
EXTERNAL NAME [UTF8Tools].[UTF8Tools].[ConversionBinaireUTF8VersNVarchar]
GO

CREATE FUNCTION [dbo].[ConversionNVarcharVersBinaireUTF8](@texte [nvarchar](max))
RETURNS [varbinary](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UTF8Tools].[UTF8Tools].[ConversionNVarcharVersBinaireUTF8]
GO

Désormais, ces fonctions, et notamment la fonction de conversion à partir d’un contenu encodé en UTF-8, nous permettent de lire correctement notre fichier.

LectureFinale

La lecture d’un fichier en mode binaire et la conversion de son contenu via une fonction codée en .Net présentent donc une solution pour lire un encodage UTF-8 directement depuis SQL Server, avec une transcodification correcte.

Je vous laisse le soin de tester ces fonctions pour résoudre vos petits soucis avec l’UTF-8, et n’hésitez pas à me dire si cela a permis de répondre à vos besoins.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Etes-vous un robot ? *Chargement du capcha...

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.