在SQL Server中统计每行数组元素数量的实现方案

问题描述 #

现有一张存储数字列表的表，数据如下：

需要统计每行的元素数量，预期结果：

解决方案 #

可以通过计算字符串中分隔符（阿拉伯逗号،）的数量来得到元素个数——因为每个元素前都有一个分隔符，分隔符的总数等于元素数量。同时处理NULL值，返回0。

SQL语句 #

SELECT
    LEN(ISNULL(numbers, '')) - LEN(REPLACE(ISNULL(numbers, ''), '،', '')) AS count
FROM your_table_name;

逻辑解释 #

1. 处理NULL值：用ISNULL(numbers, '')将NULL转换为空字符串，避免LEN函数返回NULL。
2. 计算分隔符数量：通过LEN(原字符串) - LEN(替换所有分隔符后的字符串)的差值，得到字符串中分隔符的总数，也就是每行的元素数量：
   • 对于NULL值，转换为空字符串后计算结果为0 - 0 = 0，符合预期；
   • 对于非NULL的字符串，比如،42593，原长度为6，替换掉逗号后长度为5，差值1就是元素数量。

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