logo标签列表

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

sqlsql-serverencodingutf-8iso-8859-1
18

我在SQL Server中有一列使用 SQL_Latin1_General_CP1_CI_AS 编码的文本。如何将文本转换并保存为 ISO 8859-1 编码？我想在SQL Server上的查询中完成这个操作。有什么提示吗？

你好。我喜欢这个游戏。当我下载它时，我甚至以为我不会很喜欢。

1
由于SQL Server实际上不支持UTF-8编码，这将是一个挑战！ - marc_s
该列的数据类型是nvarchar、varchar还是varbinary？它是存储为纯文本，还是百分号编码？ - Anthony Faull
它以纯文本形式存储，类型为文本。我在问题中添加了一小段代码。 - bobK
7个回答
43

我已经编写了一个函数来修复存储在varchar字段中的UTF-8文本。

您可以按照以下方式使用它来检查修复后的值：

CREATE TABLE #Table1 (Column1 varchar(max))

INSERT #Table1
VALUES ('Olá. Gostei do jogo. Quando "baixei" até achei que não iria curtir muito')

SELECT *, NewColumn1 = dbo.DecodeUTF8String(Column1)
FROM Table1
WHERE Column1 <> dbo.DecodeUTF8String(Column1)

输出：

Column1
-------------------------------
Olá. Gostei do jogo. Quando "baixei" até achei que não iria curtir muito

NewColumn1
-------------------------------
Olá. Gostei do jogo. Quando "baixei" até achei que não iria curtir muito

代码：

CREATE FUNCTION dbo.DecodeUTF8String (@value varchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    -- Transforms a UTF-8 encoded varchar string into Unicode
    -- By Anthony Faull 2014-07-31
    DECLARE @result nvarchar(max);

    -- If ASCII or null there's no work to do
    IF (@value IS NULL
        OR @value NOT LIKE '%[^ -~]%' COLLATE Latin1_General_BIN
    )
        RETURN @value;

    -- Generate all integers from 1 to the length of string
    WITH e0(n) AS (SELECT TOP(POWER(2,POWER(2,0))) NULL FROM (VALUES (NULL),(NULL)) e(n))
        , e1(n) AS (SELECT TOP(POWER(2,POWER(2,1))) NULL FROM e0 CROSS JOIN e0 e)
        , e2(n) AS (SELECT TOP(POWER(2,POWER(2,2))) NULL FROM e1 CROSS JOIN e1 e)
        , e3(n) AS (SELECT TOP(POWER(2,POWER(2,3))) NULL FROM e2 CROSS JOIN e2 e)
        , e4(n) AS (SELECT TOP(POWER(2,POWER(2,4))) NULL FROM e3 CROSS JOIN e3 e)
        , e5(n) AS (SELECT TOP(POWER(2.,POWER(2,5)-1)-1) NULL FROM e4 CROSS JOIN e4 e)
    , numbers(position) AS
    (
        SELECT TOP(DATALENGTH(@value)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM e5
    )
    -- UTF-8 Algorithm (http://en.wikipedia.org/wiki/UTF-8)
    -- For each octet, count the high-order one bits, and extract the data bits.
    , octets AS
    (
        SELECT position, highorderones, partialcodepoint
        FROM numbers a
        -- Split UTF8 string into rows of one octet each.
        CROSS APPLY (SELECT octet = ASCII(SUBSTRING(@value, position, 1))) b
        -- Count the number of leading one bits
        CROSS APPLY (SELECT highorderones = 8 - FLOOR(LOG( ~CONVERT(tinyint, octet) * 2 + 1)/LOG(2))) c
        CROSS APPLY (SELECT databits = 7 - highorderones) d
        CROSS APPLY (SELECT partialcodepoint = octet % POWER(2, databits)) e
    )
    -- Compute the Unicode codepoint for each sequence of 1 to 4 bytes
    , codepoints AS
    (
        SELECT position, codepoint
        FROM
        (
            -- Get the starting octect for each sequence (i.e. exclude the continuation bytes)
            SELECT position, highorderones, partialcodepoint
            FROM octets
            WHERE highorderones <> 1
        ) lead
        CROSS APPLY (SELECT sequencelength = CASE WHEN highorderones in (1,2,3,4) THEN highorderones ELSE 1 END) b
        CROSS APPLY (SELECT endposition = position + sequencelength - 1) c
        CROSS APPLY
        (
            -- Compute the codepoint of a single UTF-8 sequence
            SELECT codepoint = SUM(POWER(2, shiftleft) * partialcodepoint)
            FROM octets
            CROSS APPLY (SELECT shiftleft = 6 * (endposition - position)) b
            WHERE position BETWEEN lead.position AND endposition
        ) d
    )
    -- Concatenate the codepoints into a Unicode string
    SELECT @result = CONVERT(xml,
        (
            SELECT NCHAR(codepoint)
            FROM codepoints
            ORDER BY position
            FOR XML PATH('')
        )).value('.', 'nvarchar(max)');

    RETURN @result;
END
GO
1
我已经为存储过程添加了一些代码注释。我还修复了在SQL Server 2012之前版本中的“LOG”错误。 - Anthony Faull
1
此函数会在破坏 XML 编码的字符上失败，我不得不参考这篇帖子以防止 XML 解析错误：https://dev59.com/SV4c5IYBdhLWcg3wD2rC#38117491 - quetzaluz
2
似乎这个函数不能处理代理字符（Unicode字符，超出基本多语言平面）。例如，尝试使用 SELECT dbo.DecodeUTF8String(N'abcÄ ƒÃ¢Ã®È™È›ðŸ˜€âœ"?')，应该产生 abcăâîșț✔?，但它只输出 abcăâîșț✔?。 - Razvan Socol
1
有等效的编码方式吗？我似乎找不到正确编码这些值的解决方案，例如“سلام جیران”、“В России Base64 кодирует вас”、“❤️⛄”，我尝试过的每种编码方法都是垃圾...也许这需要一个问题。我可以在.NET、C++等中对它们进行编码，然后得到一个字符串，这个答案可以解码。“2LPZhNin2YUg2KzbjNix2KfZhg==”、“0JIg0KDQvtGB0YHQuNC4IEJhc2U2NCDQutC+0LTQuNGA0YPQtdGCINCy0LDRgQ==”和“4p2k77iP8J+SpfCfpKrwn6aM8J+OheKbhPCfjoTwn6SQ8J+ZiPCfmYnwn5mK8J+SqQ==”。 - Hunter-Orionnoir
我已经将我的问题作为一个问题发布在了上一个评论中。它的范围可能需要这样做。https://stackoverflow.com/questions/59223633/how-to-base64-encode-extended-character-sets-properly-using-sql-t-sql@AnthonyFaull，你对这个有什么指针吗？ - Hunter-Orionnoir
显示剩余4条评论
9

Jason Penny也编写了一个将UTF-8转换为Unicode的SQL函数（MIT许可证），在我进行简单示例测试后，该函数有效：

CREATE FUNCTION dbo.UTF8_TO_NVARCHAR(@in VarChar(MAX))
   RETURNS NVarChar(MAX)
AS
BEGIN
   DECLARE @out NVarChar(MAX), @i int, @c int, @c2 int, @c3 int, @nc int

   SELECT @i = 1, @out = ''

   WHILE (@i <= Len(@in))
   BEGIN
      SET @c = Ascii(SubString(@in, @i, 1))

      IF (@c < 128)
      BEGIN
         SET @nc = @c
         SET @i = @i + 1
      END
      ELSE IF (@c > 191 AND @c < 224)
      BEGIN
         SET @c2 = Ascii(SubString(@in, @i + 1, 1))

         SET @nc = (((@c & 31) * 64 /* << 6 */) | (@c2 & 63))
         SET @i = @i + 2
      END
      ELSE
      BEGIN
         SET @c2 = Ascii(SubString(@in, @i + 1, 1))
         SET @c3 = Ascii(SubString(@in, @i + 2, 1))

         SET @nc = (((@c & 15) * 4096 /* << 12 */) | ((@c2 & 63) * 64 /* << 6 */) | (@c3 & 63))
         SET @i = @i + 3
      END

      SET @out = @out + NChar(@nc)
   END
   RETURN @out
END
GO

Anthony所勾选的答案“看起来”对我来说更好，但如果进行转换，请运行两个并调查任何差异？！
此外，我们使用了下面非常丑陋的代码来检测作为UTF-8编码的BMP页面Unicode字符，然后将其从varchar转换为nvarchar字段，可以转换为UCS-16。
LIKE (N'%[' + CONVERT(NVARCHAR,(CHAR(192))) + CONVERT(NVARCHAR,(CHAR(193))) + CONVERT(NVARCHAR,(CHAR(194))) + CONVERT(NVARCHAR,(CHAR(195))) + CONVERT(NVARCHAR,(CHAR(196))) + CONVERT(NVARCHAR,(CHAR(197))) + CONVERT(NVARCHAR,(CHAR(198))) + CONVERT(NVARCHAR,(CHAR(199))) + CONVERT(NVARCHAR,(CHAR(200))) + CONVERT(NVARCHAR,(CHAR(201))) + CONVERT(NVARCHAR,(CHAR(202))) + CONVERT(NVARCHAR,(CHAR(203))) + CONVERT(NVARCHAR,(CHAR(204))) + CONVERT(NVARCHAR,(CHAR(205))) + CONVERT(NVARCHAR,(CHAR(206))) + CONVERT(NVARCHAR,(CHAR(207))) + CONVERT(NVARCHAR,(CHAR(208))) + CONVERT(NVARCHAR,(CHAR(209))) + CONVERT(NVARCHAR,(CHAR(210))) + CONVERT(NVARCHAR,(CHAR(211))) + CONVERT(NVARCHAR,(CHAR(212))) + CONVERT(NVARCHAR,(CHAR(213))) + CONVERT(NVARCHAR,(CHAR(214))) + CONVERT(NVARCHAR,(CHAR(215))) + CONVERT(NVARCHAR,(CHAR(216))) + CONVERT(NVARCHAR,(CHAR(217))) + CONVERT(NVARCHAR,(CHAR(218))) + CONVERT(NVARCHAR,(CHAR(219))) + CONVERT(NVARCHAR,(CHAR(220))) + CONVERT(NVARCHAR,(CHAR(221))) + CONVERT(NVARCHAR,(CHAR(222))) + CONVERT(NVARCHAR,(CHAR(223))) + CONVERT(NVARCHAR,(CHAR(224))) + CONVERT(NVARCHAR,(CHAR(225))) + CONVERT(NVARCHAR,(CHAR(226))) + CONVERT(NVARCHAR,(CHAR(227))) + CONVERT(NVARCHAR,(CHAR(228))) + CONVERT(NVARCHAR,(CHAR(229))) + CONVERT(NVARCHAR,(CHAR(230))) + CONVERT(NVARCHAR,(CHAR(231))) + CONVERT(NVARCHAR,(CHAR(232))) + CONVERT(NVARCHAR,(CHAR(233))) + CONVERT(NVARCHAR,(CHAR(234))) + CONVERT(NVARCHAR,(CHAR(235))) + CONVERT(NVARCHAR,(CHAR(236))) + CONVERT(NVARCHAR,(CHAR(237))) + CONVERT(NVARCHAR,(CHAR(238))) + CONVERT(NVARCHAR,(CHAR(239)))
    + N'][' + CONVERT(NVARCHAR,(CHAR(128))) + CONVERT(NVARCHAR,(CHAR(129))) + CONVERT(NVARCHAR,(CHAR(130))) + CONVERT(NVARCHAR,(CHAR(131))) + CONVERT(NVARCHAR,(CHAR(132))) + CONVERT(NVARCHAR,(CHAR(133))) + CONVERT(NVARCHAR,(CHAR(134))) + CONVERT(NVARCHAR,(CHAR(135))) + CONVERT(NVARCHAR,(CHAR(136))) + CONVERT(NVARCHAR,(CHAR(137))) + CONVERT(NVARCHAR,(CHAR(138))) + CONVERT(NVARCHAR,(CHAR(139))) + CONVERT(NVARCHAR,(CHAR(140))) + CONVERT(NVARCHAR,(CHAR(141))) + CONVERT(NVARCHAR,(CHAR(142))) + CONVERT(NVARCHAR,(CHAR(143))) + CONVERT(NVARCHAR,(CHAR(144))) + CONVERT(NVARCHAR,(CHAR(145))) + CONVERT(NVARCHAR,(CHAR(146))) + CONVERT(NVARCHAR,(CHAR(147))) + CONVERT(NVARCHAR,(CHAR(148))) + CONVERT(NVARCHAR,(CHAR(149))) + CONVERT(NVARCHAR,(CHAR(150))) + CONVERT(NVARCHAR,(CHAR(151))) + CONVERT(NVARCHAR,(CHAR(152))) + CONVERT(NVARCHAR,(CHAR(153))) + CONVERT(NVARCHAR,(CHAR(154))) + CONVERT(NVARCHAR,(CHAR(155))) + CONVERT(NVARCHAR,(CHAR(156))) + CONVERT(NVARCHAR,(CHAR(157))) + CONVERT(NVARCHAR,(CHAR(158))) + CONVERT(NVARCHAR,(CHAR(159))) + CONVERT(NVARCHAR,(CHAR(160))) + CONVERT(NVARCHAR,(CHAR(161))) + CONVERT(NVARCHAR,(CHAR(162))) + CONVERT(NVARCHAR,(CHAR(163))) + CONVERT(NVARCHAR,(CHAR(164))) + CONVERT(NVARCHAR,(CHAR(165))) + CONVERT(NVARCHAR,(CHAR(166))) + CONVERT(NVARCHAR,(CHAR(167))) + CONVERT(NVARCHAR,(CHAR(168))) + CONVERT(NVARCHAR,(CHAR(169))) + CONVERT(NVARCHAR,(CHAR(170))) + CONVERT(NVARCHAR,(CHAR(171))) + CONVERT(NVARCHAR,(CHAR(172))) + CONVERT(NVARCHAR,(CHAR(173))) + CONVERT(NVARCHAR,(CHAR(174))) + CONVERT(NVARCHAR,(CHAR(175))) + CONVERT(NVARCHAR,(CHAR(176))) + CONVERT(NVARCHAR,(CHAR(177))) + CONVERT(NVARCHAR,(CHAR(178))) + CONVERT(NVARCHAR,(CHAR(179))) + CONVERT(NVARCHAR,(CHAR(180))) + CONVERT(NVARCHAR,(CHAR(181))) + CONVERT(NVARCHAR,(CHAR(182))) + CONVERT(NVARCHAR,(CHAR(183))) + CONVERT(NVARCHAR,(CHAR(184))) + CONVERT(NVARCHAR,(CHAR(185))) + CONVERT(NVARCHAR,(CHAR(186))) + CONVERT(NVARCHAR,(CHAR(187))) + CONVERT(NVARCHAR,(CHAR(188))) + CONVERT(NVARCHAR,(CHAR(189))) + CONVERT(NVARCHAR,(CHAR(190))) + CONVERT(NVARCHAR,(CHAR(191)))
    + N']%') COLLATE Latin1_General_BIN

以上内容：
  • 检测编码U+0080到U+FFFF的多字节序列（U+0080到U+07FF编码为110xxxxx 10xxxxxx，U+0800到U+FFFF编码为1110xxxx 10xxxxxx 10xxxxxx）
  • 即它检测后跟十六进制字节0xC0至0xEF的十六进制字节0x80至0xBF
  • 忽略ASCII控制字符U+0000到U+001F
  • 忽略已正确编码为Unicode>=U+0100（即非UTF-8）的字符
  • 忽略Unicode字符U+0080到U+00FF，如果它们似乎不是UTF-8序列的一部分，例如“coöperatief”。
  • 不使用LIKE“%[X-Y]”，其中X=0x80至Y=0xBF，因为可能存在排序问题
  • 使用CONVERT(VARCHAR,CHAR(X))而不是NCHAR，因为我们遇到了NCHAR被转换为错误值的问题（对于某些值）。
  • 忽略大于U+FFFF的UTF字符（有第一个字节为0xF0到0xFD的4到6个字节序列）
这个对我有用，与安东尼·福尔的答案相反。感谢@robocat。 - CLS
4
我创造了一种解决方案，它也能处理4字节序列（如表情符号），这是通过结合@robocat的回答，从 https://github.com/benkasminbullock/unicode-c/blob/master/unicode.c 中获取的更多逻辑以及从https://dba.stackexchange.com/questions/139551/how-do-i-set-a-sql-server-unicode-nvarchar-string-to-an-emoji-or-supplementary中解决扩展Unicode字符编码问题的解决方案。虽然不够快或美观，但对我来说有效。此特定解决方案在找到未知字节时包括Unicode替换字符。在这些情况下，最好抛出异常或将字节保留为原样，因为未来的编码可能有所偏差，但我的用例中我更喜欢这种方式。
-- Started with https://dev59.com/cV4c5IYBdhLWcg3wOoEH
-- Modified following source in https://github.com/benkasminbullock/unicode-c/blob/master/unicode.c
-- Made characters > 65535 work using https://dba.stackexchange.com/questions/139551/how-do-i-set-a-sql-server-unicode-nvarchar-string-to-an-emoji-or-supplementary
CREATE FUNCTION dbo.UTF8_TO_NVARCHAR(@in VarChar(MAX)) RETURNS NVarChar(MAX) AS
BEGIN
    DECLARE @out NVarChar(MAX), @thisOut NVARCHAR(MAX), @i int, @c int, @c2 int, @c3 int, @c4 int

    SELECT @i = 1, @out = ''

    WHILE (@i <= Len(@in)) BEGIN
        SET @c = Ascii(SubString(@in, @i, 1))

        IF @c <= 0x7F BEGIN
            SET @thisOut = NCHAR(@c)
            SET @i = @i + 1
        END
        ELSE IF @c BETWEEN 0xC2 AND 0xDF BEGIN
            SET @c2 = Ascii(SubString(@in, @i + 1, 1))
            IF @c2 < 0x80 OR @c2 > 0xBF BEGIN
                SET @thisOut = NCHAR(0xFFFD)
                SET @i = @i + 1
            END
            ELSE BEGIN
                SET @thisOut = NCHAR(((@c & 31) * 64 /* << 6 */) | (@c2 & 63))
                SET @i = @i + 2
            END
        END
        ELSE IF @c BETWEEN 0xE0 AND 0xEF BEGIN
            SET @c2 = Ascii(SubString(@in, @i + 1, 1))
            SET @c3 = Ascii(SubString(@in, @i + 2, 1))
            IF @c2 < 0x80 OR @c2 > 0xBF OR @c3 < 0x80 OR (@c = 0xE0 AND @c2 < 0xA0) BEGIN
                SET @thisOut = NCHAR(0xFFFD)
                SET @i = @i + 1
            END
            ELSE BEGIN
                SET @thisOut = NCHAR(((@c & 15) * 4096 /* << 12 */) | ((@c2 & 63) * 64 /* << 6 */) | (@c3 & 63))
                SET @i = @i + 3
            END
        END
        ELSE IF @c BETWEEN 0xF0 AND 0xF4 BEGIN
            SET @c2 = Ascii(SubString(@in, @i + 1, 1))
            SET @c3 = Ascii(SubString(@in, @i + 2, 1))
            SET @c4 = Ascii(SubString(@in, @i + 3, 1))
            IF @c2 < 0x80 OR @c2 >= 0xC0 OR @c3 < 0x80 OR @c3 >= 0xC0 OR @c4 < 0x80 OR @c4 >= 0xC0 OR (@c = 0xF0 AND @c2 < 0x90) BEGIN
                SET @thisOut = NCHAR(0xFFFD)
                SET @i = @i + 1
            END
            ELSE BEGIN
                DECLARE @nc INT = (((@c & 0x07) * 262144 /* << 18 */) | ((@c2 & 0x3F) * 4096 /* << 12 */) | ((@c3 & 0x3F) * 64) | (@c4 & 0x3F))
                DECLARE @HighSurrogateInt INT = 55232 + (@nc / 1024), @LowSurrogateInt INT = 56320 + (@nc % 1024)
                SET @thisOut = NCHAR(@HighSurrogateInt) + NCHAR(@LowSurrogateInt)
                SET @i = @i + 4
            END
        END
        ELSE BEGIN
            SET @thisOut = NCHAR(0xFFFD)
            SET @i = @i + 1
        END

        SET @out = @out + @thisOut
    END
    RETURN @out
END
GO
1
这是我写的适用于SQL Server 2017的行内表值函数（TVF）版本。它仅限于4000字节的输入字符串，因为这已经足够满足我的需求了。通过限制输入大小并将其编写为TVF，使得该版本比迄今为止发布的缩放值函数快得多。此外，它还处理四字节的UTF-8序列（例如由表情符号创建的序列），这些序列无法在UCS-2字符串中表示，因此会输出替换字符。
CREATE OR ALTER FUNCTION [dbo].[fnUTF8Decode](@UTF8 VARCHAR(4001)) RETURNS TABLE AS RETURN

/*  Converts a UTF-8 encoded VARCHAR to NVARCHAR (UCS-2).  Based on UTF-8 documentation on Wikipedia and the 
    code/discussion at https://dev59.com/cV4c5IYBdhLWcg3wOoEH#31064459.
    
    One can quickly detect strings that need conversion using the following expression:

        <FIELD> LIKE CONCAT('%[', CHAR(192), '-', CHAR(255), ']%') COLLATE Latin1_General_BIN. 

    Be aware, however, that this may return true for strings that this function has already converted to UCS-2.
    See robocat's answer on the above referenced Stack Overflow thread for a slower but more robust expression.

    Notes/Limitations

    1)  Written as a inline table-valued function for optimized performance.  
    
    2)  Only tested on a database with SQL_Latin1_General_CP1_CI_AS collation.  More specifically, this was
        not designed to output Supplementary Characters and converts all such UTF-8 sequences to �.

    3)  Empty input strings, '', and strings with nothing but invalid UTF-8 chars are returned as NULL.

    4)  Assumes input is UTF-8 compliant.  For example, extended ASCII characters such as en dash CHAR(150)
        are not allowed unless part of a multi-byte sequence and will be skipped otherwise.  In other words:
            
            SELECT * FROM dbo.fnUTF8Decode(CHAR(150)) -> NULL
    
    5)  Input should be limited to 4000 characters to ensure that output will fit in NVARCHAR(4000), which is
        what STRING_AGG outputs when fed a sequence of NVARCHAR(1) characters generated by NCHAR.  However,
        T-SQL silently truncates overlong parameters so we've declared our input as VARCHAR(4001) to allow 
        STRING_AGG to generate an error on overlong input.  If we didn't do this, callers would never be
        notified about truncation.

    6)  If we need to process more than 4000 chars in the future, we'll need to change input to VARCHAR(MAX) and 
        CAST the CASE WHEN expression to NVARCHAR(MAX) to force STRING_AGG to output NVARCHAR(MAX).  Note that
        this change will significantly degrade performance, which is why we didn't do it in the first place.

    7)  Due to use of STRING_AGG, this is only compatible with SQL 2017.  It will probably work fine on 2019
        but that version has native UTF-8 support so you're probably better off using that.  For earlier versions,
        replace STRING_AGG with a CLR equivalent (ms-sql-server-group-concat-sqlclr) or FOR XML PATH(''), TYPE...
*/
SELECT      STRING_AGG (
                CASE 
                    WHEN A1 & 0xF0 = 0xF0 THEN  --Four byte sequences (like emoji) can't be represented in UCS-2
                        NCHAR(0xFFFD)           --Output U+FFFD (Replacement Character) instead
                    WHEN A1 & 0xE0 = 0xE0 THEN  --Three byte sequence; get/combine relevant bits from A1-A3
                        NCHAR((A1 & 0x0F) * 4096 | (A2 & 0x3F) * 64 | (A3 & 0x3F))
                    WHEN A1 & 0xC0 = 0xC0 THEN  --Two byte sequence; get/combine relevant bits from A1-A2
                        NCHAR((A1 & 0x3F) * 64 | (A2 & 0x3F))
                    ELSE NCHAR(A1)              --Regular ASCII character; output as is
                END
            , '') UCS2
FROM        dbo.fnNumbers(ISNULL(DATALENGTH(@UTF8), 0))
CROSS APPLY (SELECT ASCII(SUBSTRING(@UTF8, I, 1)) A1, ASCII(SUBSTRING(@UTF8, I + 1, 1)) A2, ASCII(SUBSTRING(@UTF8, I + 2, 1)) A3) A
WHERE       A1 <= 127 OR A1 >= 192              --Output only ASCII chars and one char for each multi-byte sequence
GO

请注意，上述需要一个“Numbers”表或生成函数。这是我使用的函数：
CREATE OR ALTER FUNCTION [dbo].[fnNumbers](@MaxNumber BIGINT) RETURNS TABLE AS RETURN

/*  Generates a table of numbers up to the specified @MaxNumber, limited to 4,294,967,296.  Useful for special case
    situations and algorithms.  Copied from https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
    with minor formatting and name changes.
*/

WITH L0 AS (
    SELECT 1 I UNION ALL SELECT 1           --Generates 2 rows
), L1 AS (
    SELECT 1 I FROM L0 CROSS JOIN L0 L      --          4 rows
), L2 AS (
    SELECT 1 I FROM L1 CROSS JOIN L1 L      --          16 rows
), L3 AS (
    SELECT 1 I FROM L2 CROSS JOIN L2 L      --          256 rows
), L4 AS (
    SELECT 1 I FROM L3 CROSS JOIN L3 L      --          65,536 rows
), L5 AS (
    SELECT 1 I FROM L4 CROSS JOIN L4 L      --          4,294,967,296 rows
), Numbers AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) I FROM L5
)
SELECT TOP (@MaxNumber) I FROM Numbers ORDER BY I
GO
1
我对代码进行了少量修改，以使用 SQL Server 2017 和 2019 中的新字符串聚合函数 string_agg。
SELECT @result=STRING_AGG(NCHAR([codepoint]),'') WITHIN GROUP (ORDER BY position ASC) 
FROM codepoints

将@result部分更改为这个。XML仍然以旧的方式工作。 在2019年，string_agg比xml版本快得多（显而易见... string_agg现在是本地的，并且不公平比较）。
0

我刚刚通过创建一个新的字段为varchar(255)，并将新字段设置为旧字段（nvarchar(255)）来成功地实现了这一点。这产生了国际地名的“美式”版本。

更新WorldCities 设置admin_correct = admin_name

 varchar(255)     nvarchar(255)
-1

我找到了需要执行的查询，只是还没有找到编码方式。

ALTER TABLE dbo.MyTable ALTER COLUMN CharCol
        varchar(10)COLLATE Latin1_General_CI_AS NOT NULL;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接