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).
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à …
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.
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.