TSQL:在字符串中查找连续数字 [英] TSQL: Find a continuous number in a string

问题描述

我需要在来自列名文件名的字符串中找到一个连续的 6 位或 7 位数字.字符串中还有其他数字和破折号(或其他字符，如下划线)，但我只需要连续数字

I need to find a continuous 6 or 7 digit number in a string from column name Filename. The string has other numbers in it with dashes(or another character, like an underscore), but I only need the continuous number

需要从文件名中提取学生 ID.(我知道数据只是哇，多个供应商，多种文件命名格式是原因.)另一种选择是只列出连续数字的起始位置.

The StudentID needs to be extracted from the filename. (I know the data is just wow, multiple vendors, multiple file naming formats is the cause.) Another option would be to just list the starting position of the continuous number.

预期结果:

实际结果:

测试代码:

DROP TABLE #StuID

CREATE TABLE #StuID (
 FILENAME VARCHAR(MAX)
,StudentID INT
)

INSERT INTO #StuID
( FILENAME  )
VALUES
 ('Smith John D, 11-23-1980, 1234567.pdf')
,('Doe Jane, _01_22_1980_123456.pdf')
,('John Doe, 567891.pdf' )

--This is what I tried.

SELECT FILENAME
, substring(FileName, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%', FileName), 8) AS StudentID
FROM #StuID

推荐答案

因为你想要 6 位或 7 位数字，case 可能是最简单的解决方案:

Because you want 6 or 7 digits, case might be the simplest solution:

SELECT FILENAME,
       (CASE WHEN FileName LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
             THEN substring(FileName, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%', FileName), 7)
             WHEN FileName LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'
             THEN substring(FileName, patindex('%[0-9][0-9][0-9][0-9][0-9]%', FileName), 6)
        END) AS StudentID                 
FROM #StuID

这篇关于TSQL:在字符串中查找连续数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！