如何在SQL SELECT语句中用INTEGER值索引STRING数组并返回对应字符串

我需要修改SQL的SELECT语句，把结果集中的INTEGER类型列iStatus，替换成用该整数值作为索引，从一个常量字符串数组中获取对应的字符串返回。

原查询语句：

SELECT iStatus FROM statusTable

我定义了模拟数组的字符串变量：

DECLARE @list varchar (23) = 'APPLE, ORANGE, PEAR, OTHER'

我已经用CASE语句实现了需求，但因为要在同一条SELECT里多次做字符串匹配，想找更简洁优雅的方案。当前的CASE语句如下：

SELECT StringStatus =
   CASE   
      WHEN iStatus = 0 THEN 'Requested' 
      WHEN iStatus = 1 THEN 'Pending'   
      WHEN iStatus = 2 THEN 'Ordered'   
      WHEN iStatus = 3 THEN 'Assigned'   
   END  

方法1：字符串分割+索引匹配（SQL Server 2016及以上可用） #

SQL Server 2016起支持STRING_SPLIT函数，但它默认不返回元素索引，需要结合ROW_NUMBER()生成对应索引后和iStatus关联：

DECLARE @list varchar(23) = 'APPLE, ORANGE, PEAR, OTHER';

WITH SplitStatus AS (
    SELECT 
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS idx  -- 减1让索引从0开始，匹配iStatus的取值
    FROM STRING_SPLIT(@list, ',')
)
SELECT 
    StringStatus = LTRIM(RTRIM(ss.value))  -- 去除元素前后空格
FROM statusTable s
LEFT JOIN SplitStatus ss ON s.iStatus = ss.idx;

注意：SQL Server 2022之前的STRING_SPLIT不保证分割结果和原字符串顺序一致，如果需要严格对应顺序，建议用下面的方法。

方法2：自定义带索引的分割函数（兼容低版本SQL Server） #

如果你的SQL Server版本低于2016，可以先自定义一个返回带索引的分割结果的函数：

CREATE FUNCTION dbo.SplitStringWithIndex(
    @inputString VARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @result TABLE (idx INT, value VARCHAR(MAX))
AS
BEGIN
    DECLARE @start INT = 1, @end INT;
    DECLARE @idx INT = 0;

    WHILE CHARINDEX(@delimiter, @inputString, @start) > 0
    BEGIN
        SET @end = CHARINDEX(@delimiter, @inputString, @start);
        INSERT INTO @result (idx, value)
        VALUES (@idx, LTRIM(RTRIM(SUBSTRING(@inputString, @start, @end - @start))));
        SET @start = @end + 1;
        SET @idx = @idx + 1;
    END

    -- 处理最后一个元素
    INSERT INTO @result (idx, value)
    VALUES (@idx, LTRIM(RTRIM(SUBSTRING(@inputString, @start, LEN(@inputString) - @start + 1))));
    RETURN;
END;

之后直接调用函数查询即可：

DECLARE @list varchar(23) = 'APPLE, ORANGE, PEAR, OTHER';

SELECT 
    StringStatus = ss.value
FROM statusTable s
LEFT JOIN dbo.SplitStringWithIndex(@list, ',') ss ON s.iStatus = ss.idx;

方法3：XML解析实现索引匹配 #

不需要自定义函数的话，可以借助XML来分割字符串并按索引取值：

DECLARE @list varchar(23) = 'APPLE, ORANGE, PEAR, OTHER';
-- 把字符串转成XML结构，同时去除元素前后空格
DECLARE @xml XML = '<root><item>' + REPLACE(LTRIM(RTRIM(@list)), ', ', '</item><item>') + '</item></root>';

SELECT 
    StringStatus = @xml.value('(/root/item[position()=sql:column("s.iStatus")+1]/text())[1]', 'VARCHAR(50)')
FROM statusTable s;

这里XML的节点位置从1开始，所以给iStatus加1来对应索引。

这些方法的优势在于，后续要修改状态对应关系时，只需要更新@list变量的内容，不用改动SELECT语句的逻辑，状态值越多，维护起来越方便。

内容的提问来源于stack exchange，提问作者PathJim