如何用SQL统计TEST表指定字符串左侧空格数并获取其位置？

针对TEST表的两个需求，给出以下SQL实现方案：

需求1：统计"EFD"左侧的空格数量 #

通过提取"EFD"之前的子串，计算该子串原长度与移除所有空格后的长度差值，即可得到左侧空格总数：

length(regexp_substr(Value, '^(.*?)EFD', 1, 1, 'n', 1)) - length(replace(regexp_substr(Value, '^(.*?)EFD', 1, 1, 'n', 1), ' ', '')) AS LEFT_SPACE_COUNT

• 说明：regexp_substr(Value, '^(.*?)EFD', 1, 1, 'n', 1) 提取"EFD"之前的所有字符；replace(..., ' ', '') 移除子串中的空格，两者长度差即为空格数。

需求2：获取"EFD"在空格分割序列中的位置 #

统计"EFD"之前的空格数量，再加1就是它在序列中的位置（示例结果为3）：

regexp_count(Value, ' ', 1, 'n', 1, instr(Value, 'EFD') - 1) + 1 AS SEQUENCE_POSITION

• 说明：instr(Value, 'EFD') - 1 定位到"EFD"前一个字符的位置；regexp_count 统计该范围内的空格数，加1后得到序列位置。

完整SQL语句（Oracle环境） #

SELECT ID,
       VALUE,
       regexp_substr(Value, 'EFD') AS "SUBSTRING",
       -- 需求1：左侧空格数
       length(regexp_substr(Value, '^(.*?)EFD', 1, 1, 'n', 1)) - length(replace(regexp_substr(Value, '^(.*?)EFD', 1, 1, 'n', 1), ' ', '')) AS LEFT_SPACE_COUNT,
       -- 需求2：序列位置
       regexp_count(Value, ' ', 1, 'n', 1, instr(Value, 'EFD') - 1) + 1 AS SEQUENCE_POSITION
FROM TEST;

如果是MySQL 8.0+环境，可调整为以下写法：

SELECT ID,
       VALUE,
       SUBSTRING_INDEX(VALUE, 'EFD', 1) AS BEFORE_EFD,
       -- 需求1：左侧空格数
       LENGTH(SUBSTRING_INDEX(VALUE, 'EFD', 1)) - LENGTH(REPLACE(SUBSTRING_INDEX(VALUE, 'EFD', 1), ' ', '')) AS LEFT_SPACE_COUNT,
       -- 需求2：序列位置
       REGEXP_COUNT(SUBSTRING_INDEX(VALUE, 'EFD', 1), ' ') + 1 AS SEQUENCE_POSITION
FROM TEST;

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