SQL:使用 Patindex-function 从字符串中提取数字

Question

字符串 #1： 国王路 8
字符串 #2： 修道院路5 字符串 #3： 卡纳比街 18-20a 字符串 #5：//
字符串 #5
： 贝克街 21a-21e

并将它们拆分为：

colA：
国王路
修道院路
卡纳比街//
贝克街

colB：
8
5 18-20a
空
21a-21e

我很感激任何帮助！

到目前为止，我试图通过使用"SUBSTRING"和"PATINDEX"将街道名称与街道编号分开，但我不知道如何完成它。

DROP TABLE IF EXISTS #Test
CREATE TABLE #Test
(
A VARCHAR(MAX) 
)
INSERT INTO #Test VALUES ('King's road 8')
INSERT INTO #Test VALUES ('Abbey road5') -- no ws between number and road
INSERT INTO #Test VALUES ('Carnaby Street 18-20A')
INSERT INTO #Test VALUES ('//')          -- if '//" is met, return NULL
INSERT INTO #Test VALUES ('Baker Str. 21a-21e')

SELECT SUBSTRING --here the fun begins, just exemplary...
(
A, PATINDEX('%[0-9]%',A), PATINDEX('%[0-9] [^0-9]%',A + 'e') - PATINDEX('%[0-9]%', A) + 1
) AS Number 
FROM  #Test

Answer 1

根据您的示例数据和预期结果，您可以使用一些相对简单的表达式来获取它。

SELECT A,
SUBSTRING( A, 0, ISNULL(NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A)+1)),
SUBSTRING( A, NULLIF(PATINDEX('%[0-9]%', A), 0), LEN(A))
FROM  #Test;

对于第一列，我从 0 开始，以避免将 1 减去到第一个数字位置。然后，我使用ISNULL(NULLIF(,0)LEN(A))的组合来分配总长度，以防没有可用的数字。

对于第二列，我仍然使用NULLIF，但我不使用ISNULL将值保留为 NULL。然后，我只需添加字符串的总长度。