{"id":1188,"date":"2015-04-23T01:18:32","date_gmt":"2015-04-22T23:18:32","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=1188"},"modified":"2020-09-01T10:07:50","modified_gmt":"2020-09-01T08:07:50","slug":"comment-traiter-des-chaines-de-caracteres-utf-8","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/comment-traiter-des-chaines-de-caracteres-utf-8\/","title":{"rendered":"Comment tra\u00eeter des cha\u00eenes de caract\u00e8res UTF-8 ?"},"content":{"rendered":"

De nombreux articles \u00e9voquent le fait que SQL Server ne supporte pas l’encodage UTF-8 mais uniquement l’encodage UTF-16. Ce support a d’ailleurs \u00e9t\u00e9 am\u00e9lior\u00e9\u00a0\u00e0 partir de SQL Server 2012 avec de nouveaux classements (voir Prise en charge d’Unicode sous SQL Server<\/a>).<\/p>\n

Mais l’exp\u00e9rience nous montre que de nombreuses donn\u00e9es que l’on cherche \u00e0 traiter sont en UTF-8, et on n’a pas n\u00e9cessairement envie de sortir la grosse artillerie (par exemple un import via SQL Server Integration Service). Je vous propose donc une mani\u00e8re simple d’importer correctement dans un script T-SQL un fichier encod\u00e9 en UTF-8.<\/p>\n

Tout d’abord, nous allons construire un petit fichier encod\u00e9 en UTF-8.\u00a0que nous chercherons ensuite \u00e0 importer proprement. Voici ici le fichier qui nous servira de base (j’utilise Notepad++<\/a> pour bien fixer le fichier dans le bon format d’encodage).<\/p>\n

\"Fichier_UTF8\"<\/a><\/p>\n

Ce fichier est bien encod\u00e9 en UTF-8, comme indiqu\u00e9 en bas \u00e0 droite de l’image. Ce fichier pr\u00e9sente notamment quelques accents, qui sont typiquement probl\u00e9matiques lorsqu’il est question d’encodages.<\/p>\n

Pour lire un fichier et positionner son contenu dans une cha\u00eene de caract\u00e8res, une des possibilit\u00e9s est d’utiliser la commande OPENROWSET (voir l’aide en ligne ici<\/a>). Mais on remarque notamment dans cette aide en ligne que cette commande, bien qu’acceptant un param\u00e8tre qui permet de pr\u00e9ciser le code page du fichier d’entr\u00e9e, ne supporte pas le format UTF-8. Le m\u00eame souci se retrouve de mani\u00e8re g\u00e9n\u00e9rale au niveau de SQL Server. UTF-16 est bien support\u00e9, mais pas UTF-8.<\/p>\n

Au final, si on prend notre fichier et que l’on cherche \u00e0 l’importer dans une cha\u00eene de caract\u00e8res, les accents ne passent pas l’\u00e9preuve avec succ\u00e8s, loin de l\u00e0 …<\/p>\n

\"Lecture1\"<\/a><\/p>\n

Etant donn\u00e9 que les fonctions int\u00e9gr\u00e9es \u00e0 SQL Server ne permettent pas d’int\u00e9grer correctement le code page UTF-8,\u00a0une solution consiste \u00e0 passer par\u00a0le d\u00e9veloppement d’une m\u00e9thode sp\u00e9cifique qui utilise la CLR.<\/p>\n

Plusieurs\u00a0solutions sont possibles, je vous propose ici le code d’une fonction\u00a0scalaire, qui prend en entr\u00e9e un binaire (le contenu du fichier) et pr\u00e9sente en sortie une chaine Unicode correctement convertie. Nous nous appuierons sur une version simplifi\u00e9e du code pr\u00e9sent\u00e9 dans cet article <\/a>du MSDN \u00e0 propos de la d\u00e9finition d’un type de donn\u00e9es utilisateur d\u00e9di\u00e9 aux cha\u00eenes UTF-8.<\/p>\n

Dans un premier temps, nous devront activer la CLR int\u00e9gr\u00e9e au moteur SQL Server afin de pouvoir utiliser des fonctions d\u00e9velopp\u00e9es en utilisant du code .Net.<\/p>\n

sp_configure 'clr enabled', 1 \r\nGO \r\nRECONFIGURE \r\nGO<\/pre>\n
Ensuite, il suffit de mettre en place deux petites fonctions utilis\u00e9es pour la conversion.<\/p>\n
 <\/p>\n
using System;\r\nusing System.Data.Sql;\r\nusing System.Data.SqlTypes;\r\nusing Microsoft.SqlServer.Server;\r\n\r\npublic sealed class UTF8Tools\r\n{\r\n\r\n    [SqlFunction(IsDeterministic = true, IsPrecise = true)]\r\n    public static SqlString ConversionBinaireUTF8VersNVarchar(SqlBinary entree)\r\n    {\r\n        SqlString sortie = (entree.IsNull) ? SqlString.Null : new SqlString(System.Text.Encoding.UTF8.GetString(entree.Value));\r\n        return sortie;\r\n    }\r\n\r\n    [SqlFunction(IsDeterministic = true, IsPrecise = true)]\r\n    public static SqlBinary ConversionNVarcharVersBinaireUTF8(SqlString entree)\r\n    {\r\n        SqlBinary sortie = (entree.IsNull) ? SqlBinary.Null : new SqlBinary(System.Text.Encoding.UTF8.GetBytes(entree.ToString()));\r\n        return sortie;\r\n    }\r\n}<\/pre>\n
Ce code permet d’avoir deux fonctions de conversion d’un contenu binaire de fichier en UTF-8 vers une cha\u00eene de caract\u00e8res et vice-versa.<\/p>\n
Pour d\u00e9ployer ces fonctions vers le serveur, il est possible de compiler et publier via Visual Studio, ou de charger depuis SSMS une assembl\u00e9e publi\u00e9e. Une autre possibilit\u00e9 est de d\u00e9ployer directement le code binaire de l’assembl\u00e9e.<\/p>\n
 <\/p>\n
SET ANSI_NULLS OFF\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER OFF\r\nGO\r\n\r\nCREATE ASSEMBLY [UTF8Tools]\r\nFROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103009B2338550000000000000000E00002210B010B00000C000000060000000000001E2A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000D02900004B000000004000000803000000000000000000000000000000000000006000000C000000982800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000240A000000200000000C000000020000000000000000000000000000200000602E72737263000000080300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001200000000000000000000000000004000004200000000000000000000000000000000002A0000000000004800000002000500CC200000CC07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330020029000000010000110F00281200000A2D18281300000A0F00281400000A6F1500000A731600000A2B057E1700000A0A062A000000133002002F000000020000110F00281800000A2D1E281300000A0F00FE16020000016F1900000A6F1A00000A731B00000A2B057E1C00000A0A062A1E02281D00000A2A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000074020000237E0000E00200006803000023537472696E67730000000048060000080000002355530050060000100000002347554944000000600600006C01000023426C6F620000000000000002000001471502000900000000FA25330016000001000000160000000200000003000000020000001D0000001000000002000000010000000200000000000A00010000000000060032002B000A005A0045000A00640045000600D900BF0006000401F20006001B01F20006003801F20006005701F20006007001F20006008901F2000600A401F2000600BF01F2000600F701D80106000B02D80106001902F20006003202F200060062024F024700760200000600A50285020600C50285020A00FE02E30206002A031E030000000001000000000001000100010110001800000005000100010050200000000096006E000A0001008820000000009600900011000200C320000000008618B2001800030000000100B80000000100B8002100B2001C002900B2001C003100B2001C003900B2001C004100B2001C004900B2001C005100B2001C005900B2001C006100B2001C006900B20021007100B2001C007900B2001C008100B2001C008900B20026009900B2002C00A100B2001800A900B2001800190013035600B10033035A0019003C035F00B100460364001100B2001C00110050036A00110013035600090055037300B1005E0377001900B2007D001900500383000900B200180020008B0031002E003300D6002E000B008C002E001300D6002E001B00E5002E002300E5002E002B00E5002E005B0003012E0083004C012E004300E5002E005300E5002E003B00EB002E006B002D012E0073003A012E007B00430140008B0031006E008700048000000100000000000000000000000000180000000400000000000000000000000100220000000000040000000000000000000000010039000000000000000000003C4D6F64756C653E0055544638546F6F6C732E646C6C0055544638546F6F6C73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C42696E61727900436F6E76657273696F6E42696E6169726555544638566572734E5661726368617200436F6E76657273696F6E4E566172636861725665727342696E6169726555544638002E63746F7200656E747265650053797374656D2E52756E74696D652E56657273696F6E696E67005461726765744672616D65776F726B4174747269627574650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C0053797374656D2E5465787400456E636F64696E67006765745F55544638006765745F56616C756500476574537472696E67004E756C6C00546F537472696E670047657442797465730000000320000000000067D38BEA76C4D441A3EFF46221AEEA030008B77A5C561934E0890600011109110D060001110D110903200001042001010E04200101020520010111490420010108240100020054020F497344657465726D696E697374696301540209497350726563697365010320000204000012590420001D050520010E1D050306110904070111090320000E0520011D050E052001011D050306110D040701110D4901001A2E4E45544672616D65776F726B2C56657273696F6E3D76342E350100540E144672616D65776F726B446973706C61794E616D65122E4E4554204672616D65776F726B20342E350E01000955544638546F6F6C73000005010000000017010012436F7079726967687420C2A920203230313500002901002466343335653836312D666537642D343838372D613738622D62353966333465303461373700000C010007312E302E302E3000000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000009B23385500000000020000001C010000B4280000B40A0000525344536C5748640BC466408AE4C6383BF11C7C01000000633A5C55736572735C41646D696E6973747261746F725C446F63756D656E74735C56697375616C2053747564696F20323031335C50726F6A656374735C55544638546F6F6C735C55544638546F6F6C735C6F626A5C52656C656173655C55544638546F6F6C732E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F829000000000000000000000E2A0000002000000000000000000000000000000000000000000000002A00000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000B00200000000000000000000B00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00410020000010053007400720069006E006700460069006C00650049006E0066006F000000EC01000001003000300030003000300034006200300000003C000A000100460069006C0065004400650073006300720069007000740069006F006E0000000000550054004600380054006F006F006C0073000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D0065000000550054004600380054006F006F006C0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A900200020003200300031003500000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000550054004600380054006F006F006C0073002E0064006C006C00000034000A000100500072006F0064007500630074004E0061006D00650000000000550054004600380054006F006F006C0073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000203A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\r\nWITH PERMISSION_SET = SAFE\r\n\r\nGO\r\n\r\nCREATE FUNCTION [dbo].[ConversionBinaireUTF8VersNVarchar](@contenu [varbinary](max))\r\nRETURNS [nvarchar](max) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT\r\nAS \r\nEXTERNAL NAME [UTF8Tools].[UTF8Tools].[ConversionBinaireUTF8VersNVarchar]\r\nGO\r\n\r\nCREATE FUNCTION [dbo].[ConversionNVarcharVersBinaireUTF8](@texte [nvarchar](max))\r\nRETURNS [varbinary](max) WITH EXECUTE AS CALLER\r\nAS \r\nEXTERNAL NAME [UTF8Tools].[UTF8Tools].[ConversionNVarcharVersBinaireUTF8]\r\nGO<\/pre>\n
D\u00e9sormais, ces fonctions, et notamment la fonction de conversion \u00e0 partir d’un contenu encod\u00e9 en UTF-8, nous permettent de lire correctement notre fichier.<\/p>\n
\"LectureFinale\"<\/a><\/p>\n
La lecture d’un fichier en mode binaire et la conversion de son contenu via une fonction cod\u00e9e en .Net pr\u00e9sentent donc une solution pour lire un encodage UTF-8 directement depuis SQL Server, avec une transcodification correcte.<\/p>\n
Je vous laisse le soin de\u00a0tester ces fonctions pour r\u00e9soudre vos petits soucis avec l’UTF-8, et n’h\u00e9sitez pas \u00e0 me dire si cela a permis de r\u00e9pondre \u00e0 vos besoins.<\/p>\n","protected":false},"excerpt":{"rendered":"
De nombreux articles \u00e9voquent le fait que SQL Server ne supporte pas l’encodage UTF-8 mais uniquement l’encodage UTF-16. Ce support a d’ailleurs \u00e9t\u00e9 am\u00e9lior\u00e9\u00a0\u00e0 partir de SQL Server 2012 avec de nouveaux classements (voir Prise en charge d’Unicode sous SQL … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1188","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1188","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=1188"}],"version-history":[{"count":28,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1188\/revisions"}],"predecessor-version":[{"id":1840,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1188\/revisions\/1840"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=1188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=1188"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=1188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}