如何在 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 中将 varbinary() 转换为 varchar(max) 时对特定于语言的字符进行编码?)

问题描述

我正在尝试将 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) 时对特定于语言的字符进行编码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！