SQL Server中如何在字符串内搜索多个数字拼写词组合

实现逻辑 #

通过统计单条文本中所有数字拼写词（zero/one/two/three/four/five/six/seven/eight/nine）的出现总次数，筛选符合阈值的记录即可，同时加入空格包裹逻辑避免误匹配嵌入在其他单词中的数字拼写片段。

全版本兼容写法 #

兼容所有SQL Server版本，直接替换表名即可使用：

SELECT *
FROM 你的表名
WHERE 
(
    -- 统计每个数字拼写词的出现次数并求和
    (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' zero ', '')))/LEN(' zero ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' one ', '')))/LEN(' one ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' two ', '')))/LEN(' two ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' three ', '')))/LEN(' three ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' four ', '')))/LEN(' four ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' five ', '')))/LEN(' five ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' six ', '')))/LEN(' six ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' seven ', '')))/LEN(' seven ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' eight ', '')))/LEN(' eight ')
    + (LEN(' ' + transcripts + ' ') - LEN(REPLACE(' ' + transcripts + ' ', ' nine ', '')))/LEN(' nine ')
) >= 4 -- 查找完整9位社保号时将阈值改为9即可

逻辑说明 #

• 给transcripts首尾各加一个空格，避免匹配到开头/结尾的数字词时漏匹配，同时防止匹配到包含数字拼写的普通单词（比如stone里的one不会被误统计）
• 对每个数字拼写词，用「替换前的总长度 - 替换掉所有该词后的长度」除以该词的长度，得到该词在文本中的出现次数
• 所有数字词的出现次数求和后，按需求设置筛选阈值即可

SQL Server 2016及以上简洁写法 #

如果数据库版本支持，可通过临时表关联简化代码：

-- 定义数字拼写词列表
DECLARE @NumWords TABLE (word VARCHAR(10))
INSERT INTO @NumWords VALUES ('zero'),('one'),('two'),('three'),('four'),('five'),('six'),('seven'),('eight'),('nine')

SELECT t.*
FROM 你的表名 t
CROSS APPLY (
    SELECT COUNT(*) as num_count
    FROM @NumWords n
    WHERE ' ' + t.transcripts + ' ' LIKE '% ' + n.word + ' %'
) cnt
WHERE cnt.num_count >=4 -- 按需调整为9即可

优化建议 #

• 标点兼容：如果文本中存在逗号、句号等标点分隔数字词，可先对transcripts做标点替换为空格的预处理，例如把' ' + transcripts + ' '修改为' ' + REPLACE(REPLACE(REPLACE(transcripts, ',', ' '), '.', ' '), '?', ' ') + ' '，可根据实际文本场景补充替换规则
• 匹配等级区分：如需区分完整9位社保号和仅后四位匹配，可新增匹配类型字段：

CASE WHEN cnt.num_count >=9 THEN '完整社保号匹配' WHEN cnt.num_count >=4 THEN '后四位匹配' ELSE '无匹配' END as match_level

• 连续匹配需求：如果要求4个/9个数字词是连续出现的，可先将文本中所有数字拼写词统一替换为#标记，再通过LIKE '%# # # #%'匹配连续4个数字的片段即可。

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