SQL — проблема с кодировкой UTF-8 в varchar/nvarchar

ОБНОВЛЕНИЕ: я только что узнал кое-что новое, и это - ммм - здорово :-)

Попробуйте эту функцию

CREATE FUNCTION dbo.Convert_utf8(@utf8 VARBINARY(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @rslt NVARCHAR(MAX);

    SELECT @rslt=
    CAST(
          --'<?xml version = "1.0" encoding = "UTF-8"?><![CDATA['
          0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E3C215B43444154415B
          --the content goes within CDATA
        + @utf8
        --']]>'
        + 0x5D5D3E
    AS XML).value('.', 'nvarchar(max)');

    RETURN @rslt;
END
GO

И назовите это так

SELECT *
      ,dbo.Convert_utf8(CAST(t.body AS XML).value('.','varbinary(max)'))
FROM @t t;

Результат

DALLAS, TX – May 7, 2019 – Covey & Park Energy Holdings LLC (“Covey Park” 

GSerg, спасибо большое! за ваш ответ ниже. Я попробовал и упростил это для работы в UDF.

Похоже, что преобразование varbinary(max) в XML полностью выполняется в среде CLR, где учитывается объявление кодировки XML. Кажется, это работает и с другими кодировками, но сейчас у меня нет времени, чтобы проверить это в целом.

Теперь остальная часть ответа

Поскольку он содержит некоторые сведения о кодировании строк, которые, возможно, стоит прочитать.

Я немного упростил ваш код:

declare @t table ( [body] nvarchar(max) ) 

insert into @t(body) 
select 'REFMTEFTLCBUWCDigJMgTWF5IDcsIDIwMTkg4oCTIENvdmV5ICYgUGFyayBFbmVyZ3kgSG9sZGluZ3MgTExDICjigJxDb3ZleSBQYXJr4oCdIA==';

SELECT  CAST(t.body AS XML).value('.','varbinary(max)')
       ,CAST(CAST(t.body AS XML).value('.','varbinary(max)') AS VARCHAR(MAX))
FROM @t t;

Вы увидите этот результат

0x44414C4C41532C20545820E28093204D617920372C203230313920E2809320436F7665792026205061726B20456E6572677920486F6C64696E6773204C4C432028E2809C436F766579205061726BE2809D20  
DALLAS, TX – May 7, 2019 – Covey & Park Energy Holdings LLC (“Covey Park†

Я поставлю первые символы более удобными для чтения

0x44414C4C41532C20545820E28093  
   D A L L A S ,   T X   â € “ 

0x44 — это D, удвоенное 0x4C — это удвоенное LL, а после пробела 0x20 мы получаем E28093. Это 3-байтовая кодовая точка для в тире. SQL-сервер вам в этом не поможет... Он интерпретирует это в 3 символа по 1 байту...

Боюсь, вам не повезло...

SQL-Server не поддерживает utf-8 строки. Существует ограниченная поддержка BCP / BULK для включения ввода из файловой системы, но строка в пределахT-SQL должна быть одной из двух поддерживаемых опций:

• (var)char, то есть расширенный ASCII. Это строго один байт на символ, и для работы с ограниченным набором иностранных символов потребуется сопоставление.
• n(var)char, то есть УКС-2 (очень похоже на UTF-16). Это строго два байта на символ и будет кодировать (почти) любой известный символ ценой удвоения размера памяти.

UTF-8 совместим с (var)char, если мы придерживаемся простая латынь и однобайтовые коды. Но любой код ASCII выше 127 приведет к проблемам (может работать с правильным сопоставлением). Но - это ваш случай - ваша строка использует многобайтовые кодовые точки. UTF-8 будет кодировать множество символов двумя и более байтами (до 4!) для одного символа.

Что ты можешь сделать

Вам придется использовать какой-нибудь движок, способный работать с UTF-8.

• CLR-функция
• Экспорт в файл и повторный импорт с использованием ограниченной поддержки (требуется v2014 SP2 или выше)
• Используйте внешний инструмент (PowerShell, C#, любой известный вам язык программирования)

И - спасибо @GSerg - еще два варианта:

• Ждите v2019. Будет специальные сопоставления, обеспечивающий встроенную поддержку utf-8 в строках T-SQL.
• Этот ответ предоставляет UDF, который может преобразовывать UTF8 в NVARCHAR. Это будет не быстро, но работает.

Общее замечание

База данных может содержать только данные хранения как есть или рабочие данные, которые вы хотите использовать тем или иным способом. Сохранение изображения как VARBINARY(MAX) — это всего лишь кусок битов. Вы бы не пытались использовать SQL-Server для распознавания изображений.

То же самое и с текстовыми данными. Если вы просто сохраните кусок текста, не имеет значения, как вы это сделаете. Но если вы хотите использовать этот текст для фильтрации, поиска или если вы хотите использовать SQL-Server для отображения этого текста, вы должны подумать о формате и требованиях к производительности.

Кодирование с переменной длиной байта не позволит использовать простой SUBSTRING('blahblah',2,3). При фиксированной длине движок может просто взять строку как массив, перейти ко второму индексу и выбрать следующие три символа. Но с переменными байтами механизм должен будет вычислить индекс, проверив все символы перед тем, может ли быть какая-либо многобайтовая кодовая точка. Это сильно замедлит работу многих строковых методов...

Лучше всего было не хранить данные в формате, который SQL-сервер не может обрабатывать (хорошо)...

Трюк с XML отлично работает, просто позвольте движку XML обрабатывать сущности символов:

declare @t table ([body] nvarchar(max));

insert into @t(body) 
values ('REFMTEFTLCBUWCDigJMgTWF5IDcsIDIwMTkg4oCTIENvdmV5ICYgUGFyayBFbmVyZ3kgSG9sZGluZ3MgTExDICjigJxDb3ZleSBQYXJr4oCdIA==');

select
    cast(
        cast('<?xml version = "1.0" encoding = "UTF-8"?><root><![CDATA[' as varbinary(max))
        +
        CAST('' as xml).value('xs:base64Binary(sql:column("body"))', 'VARBINARY(MAX)')
        +
        cast(']]></root>' as varbinary(max))
    as xml).value('.', 'nvarchar(max)')
from
@t;

Важными частями здесь являются:

• отсутствиеN перед строковыми литералами
• encoding = "UTF-8"
• Тот факт, что мы знаем, что символы из элемента объявления XML имеют то же представление UTF-8, что и в latin1, поэтому приведение их к varbinary дает действительный UTF-8
• Блок <![CDATA]]>.

Обратите внимание, что это все еще не более чем взлом. Как только вы задействуете XML, на вас распространяются ограничения XML, и если ваша строка содержит символы, не представленные в XML, этот тип преобразования XML не удастся с

XML parsing: line 1, character 54, illegal xml character

Если у вас есть SQL Server 2019, вы можете создать другую базу данных с UTF8 в качестве сопоставления по умолчанию и создать там простую функцию:

USE UTF8_DATABASE
GO

CREATE OR ALTER FUNCTION dbo.VarBinaryToUTF8
  (@UTF8 VARBINARY(MAX))
  RETURNS VARCHAR(MAX)
AS
BEGIN
  RETURN CAST(@UTF8 AS VARCHAR(MAX));
END;

Вы бы позвонили

SELECT
  UTF8_DATABASE.dbo.VarBinaryToUTF8
  (
    CAST('' as xml).value('xs:base64Binary(sql:column("body"))','varbinary(max)')
  )
FROM
  @t

Это работает, потому что SQL-сервер использует сопоставление по умолчанию конкретной базы данных для своих переменных и возвращаемых значений функций. Вы должны сохранить результат в NVARCHAR или UTF8 сопоставленной базе данных «VARCHAR in your non-UTF8».