sql - 从 OPENJSON SQL Server 检索特殊字符的问题

Question

我正在解析 JSON 文件中的数据，当我最终加载到我的表中时遇到问题，其中有特殊字符（如 ñ）的数据显示为 ñ。

代码在 NVARCHAR 中，我不确定为什么它没有正确拉动

INSERT INTO @recordsInfo
    (
        messageId,
        messageType,
        messageVariation,
        windowEnvelope,
        recipientFirstName,
        recipientLastName,
        recipientAddressLine1,
        recipientAddressLine2,
        recipientCity,
        recipientState,
        recipientZip,
        recipientZipSuffix
    )
    SELECT messageId,
           messageType,
           messageVariation,
           windowEnvelope,
           recipientFirstName,
           recipientLastName,
           recipientAddressLine1,
           recipientAddressLine2,
           recipientCity,
           recipientState,
           recipientZip,
           recipientZipSuffix
    FROM
        OPENJSON(@ValueR)
        WITH
        (
            messageId NVARCHAR(100),
            messageType NVARCHAR(100) '$.messageDetails.messageType',
            messageVariation NVARCHAR(100) '$.messageDetails.messageVariation',
            windowEnvelope NVARCHAR(100) '$.messageDetails.windowEnvelope',
            recipientFirstName NVARCHAR(100) '$."shippingDetails"."recipientFirstName"',
            recipientLastName NVARCHAR(100) '$."shippingDetails"."recipientLastName"',
            recipientAddressLine1 NVARCHAR(1000) '$.shippingDetails.recipientAddressLine1',
            recipientAddressLine2 NVARCHAR(100) '$.shippingDetails.recipientAddressLine2',
            recipientCity NVARCHAR(100) '$.shippingDetails.recipientCity',
            recipientState NVARCHAR(100) '$.shippingDetails.recipientState',
            recipientZip NVARCHAR(100) '$.shippingDetails.recipientZip',
            recipientZipSuffix NVARCHAR(100) '$.shippingDetails.recipientZipSuffix'
        );

DECLARE @recordsInfo TABLE
(
    RecID INT IDENTITY(1, 1),
    messageId NVARCHAR(100),
    messageType NVARCHAR(100),
    messageVariation NVARCHAR(100),
    windowEnvelope NVARCHAR(100),
    recipientFirstName NVARCHAR(100),
    recipientLastName NVARCHAR(100),
    recipientAddressLine1 NVARCHAR(1000),
    recipientAddressLine2 NVARCHAR(100),
    recipientCity NVARCHAR(100),
    recipientState NVARCHAR(100),
    recipientZip NVARCHAR(100),
    recipientZipSuffix NVARCHAR(100)
);

RecipientFirstName 是我正在查看的第一个

标签： sqljsonsql-server

Answer 1

JSON 通常是 UTF-8。只有在 2019 版本中，SQL Server 才能理解 UTF-8，因此在此之前，您必须手动完成。

将 SQL Server 中的文本值从 UTF8 转换为 ISO 8859-1