如何在 SQL Server 2012 中将 varbinary() 转换为 varchar(max) 时对特定于语言的字符进行编码? [英] How to encode language specific chars while converting varbinary() to varchar(max) in SQL Server 2012?

问题描述

我正在尝试将 SQL Server 2012 中的数据库列 DATA 从 varbinary() 转换为 varchar(max).

I am trying to convert a database column DATA from varbinary() to varchar(max) in SQL Server 2012.

我正在使用此代码来处理转换:

I am using this code to handle the conversion:

SELECT CONVERT(VARCHAR(MAX), DATA) FROM [dbo].[TABLE_NAME]

结果行如下:

VW 6501 Çamaşır

我在使用语言特定字符时遇到问题(目前我的语言是土耳其语)

I am having trouble with language specific characters (language is Turkish in my case for now)

如何解决 SQL Server 2012 中的这个编码问题?

How do I get over this encoding problem in SQL Server 2012?

考虑到任何给定语言的数据丢失/编码问题，是否有针对任何语言进行这种转换的通用方法?

Is there a generic way to do this conversion for any language, considering loss of data/encoding problems for any given language?

这可能听起来像一个新手问题，但我真的很感激任何建议或答案.

This may sound like a rookie question but I really would appreciate any suggestions or answer.

谢谢，

推荐答案

一般来说，SQL Server 并不重视 UTF-8.但是，.NET 有方法可以做到这一点，您可以通过 CLR 集成获得它们.

In general, SQL Server does not hold UTF-8 in high regard. However, .NET has methods to do this and you can get at them via CLR integration.

使用 C# 编译:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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

namespace UtfLib
{
    public static class UtfMethods
    {
        [SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlBinary NVarCharToUtf8(SqlString inputText)
        {
            if (inputText.IsNull)
                return new SqlBinary(); // (null)

            return new SqlBinary(Encoding.UTF8.GetBytes(inputText.Value));
        }

        [SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlString Utf8ToNVarChar(SqlBinary inputBytes)
        {
            if (inputBytes.IsNull)
                return new SqlString(); // (null)

            return new SqlString(Encoding.UTF8.GetString(inputBytes.Value));
        }
    }
}

将程序集导入数据库并创建外部函数:

Import the assembly into your database and create the external functions:

CREATE ASSEMBLY UtfLib
FROM 'C:UtfLib.dll'
GO
CREATE FUNCTION NVarCharToUtf8 (@InputText NVARCHAR(MAX))
RETURNS VARBINARY(MAX)
AS EXTERNAL NAME UtfLib.[UtfLib.UtfMethods].NVarCharToUtf8
GO
CREATE FUNCTION Utf8ToNVarChar (@InputBytes VARBINARY(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME UtfLib.[UtfLib.UtfMethods].Utf8ToNVarChar

最后一步，你必须启用clr

Last step, you have to enable clr

sp_configure 'clr enabled',1
GO
RECONFIGURE
GO
sp_configure 'clr enabled'  -- make sure it took
GO

瞧！

SELECT dbo.Utf8ToNVarChar(DATA) FROM [dbo].[TABLE_NAME]

这篇关于如何在 SQL Server 2012 中将 varbinary() 转换为 varchar(max) 时对特定于语言的字符进行编码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！