使用T-SQL检索具有许多场景的字符串的一部分

我相信这将满足您的所有标准,但一定要检查它对边缘案件没有提出在这里,以确保它做你所期望的。

DECLARE @table as TABLE (code VARCHAR(40))

INSERT INTO @table
(code)
Values
('AB 123456.123'),
('AB 123456/123'),
('AB 123456-123'),
('AB B0-23456.123'),
('AB 1234 5678 954 3214.123'),
('AB 1234 5678 9545/3214.123'),
('AB 123456 123'),
('AB.123456 123'),
('AB..123456 123'),
('AB..1C23456 123')


SELECT 
code as [input],
SUBSTRING(code,PATINDEX('%[0-9]%', code),
CASE WHEN PATINDEX('%[ -][0-9][0-9][0-9][0-9]%',SUBSTRING(code,PATINDEX('%[0-9]%', code),LEN(code))) <> 0
THEN 
    CASE WHEN PATINDEX('%[.,/]%',SUBSTRING(code,PATINDEX('%[0-9]%', code),LEN(code))) < ISNULL(NULLIF(PATINDEX('%[ -][0-9][0-9][0-9][^0-9]%',SUBSTRING(code,PATINDEX('%[0-9]%', code),LEN(code))),0),LEN(code))
    THEN PATINDEX('%[.,/]%',SUBSTRING(code,PATINDEX('%[0-9]%', code),LEN(code)))-1
    ELSE ISNULL(NULLIF(PATINDEX('%[ -][0-9][0-9][0-9][^0-9]%',SUBSTRING(code,PATINDEX('%[0-9]%', code),LEN(code))),0),LEN(code))
    END
ELSE PATINDEX('%[. ,/-]%', SUBSTRING(code,PATINDEX('%[0-9]%', code),LEN(code)))-1
END
) as [output]
FROM @table

老实说:这是一场噩梦。 T-SQL

DECLARE @mockup TABLE(ID INT IDENTITY, YourString VARCHAR(1000));
INSERT INTO @mockup VALUES
 ('AB 123456.123')                 
,('AB 123456/123')                 
,('AB 123456-123')                 
,('AB B0-23456.123')               
,('AB 1234 5678 9545 3214.123')    
,('AB 123456 123')                 
,('AB.123456 123')                 
,('AB..123456 123')                
,('AB..1C23456 123')
,('AB 1234 5678 954 3214-12345.123');

WITH CutForRules AS
(
    SELECT t.ID
          ,t.YourString
          ,ROW_NUMBER() OVER(PARTITION BY t.ID ORDER BY (SELECT (NULL))) FragmentIndex
          ,c AsXml
          ,d.value('text()[1]','varchar(100)') Fragment
          ,ISNUMERIC(d.value('text()[1]','varchar(100)')) FragmentIsNum
          ,LEN(d.value('text()[1]','varchar(100)')) FragmentLength
          ,d.value('@dlmt','varchar(10)') Delimiter
    FROM @mockup t
    CROSS APPLY(SELECT REVERSE(SUBSTRING(t.YourString,PATINDEX('%[0-9]%',t.YourString),1000))) A(a) 
    CROSS APPLY(SELECT REVERSE(SUBSTRING(a,PATINDEX('%[ /.-]%',a)+1,1000))) B(b)    
    CROSS APPLY(SELECT CAST('<x>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(b,'/','|'),' ','</x><x dlmt=" ">'),'.','</x><x dlmt=".">'),'-','</x><x dlmt="-">'),'|','</x><x dlmt="/">') + '</x>' AS XML)) C(c)
    CROSS APPLY c.nodes('/x') D(d)
)
SELECT t1.ID
      ,t1.YourString
      ,(
        SELECT CONCAT(t2.Delimiter,t2.Fragment)
        FROM CutForRules t2
        WHERE t1.ID=t2.ID
          AND (t2.FragmentIndex<(SELECT MIN(t3.FragmentIndex) 
                                 FROM CutForRules t3 
                                 WHERE t3.ID=t1.ID
                                   AND t3.FragmentIndex>t2.FragmentIndex
                                   AND t3.Delimiter=' '
                                   AND t3.FragmentLength<4
                                   AND t3.FragmentIsNum=1)
               OR NOT EXISTS(SELECT 1 FROM CutForRules t4 WHERE t4.ID=t1.ID AND t4.Delimiter=' ' AND t4.FragmentLength<4)
              )
        ORDER BY t2.FragmentIndex
        FOR XML PATH('')
       )
FROM CutForRules t1
GROUP BY t1.ID,t1.YourString
ORDER BY t1.ID;

你可以放一个 SELECT * FROM CutForRules 查看我用于此的中间结果集。

但我很肯定,你会想出

只是想弄清楚:我现在出局了;-)

更新:一些解释

cte 剪切规则 将返回此集合作为我的测试数据:

+----+---------------------------------+---------+---+---+------+
|    | YourString                      |Fragment | N | L | Delm |
+----+---------------------------------+---------+---+---+------+
| 1  | AB 123456.123                   | 123456  | 1 | 6 | NULL |
+----+---------------------------------+---------+---+---+------+
| 2  | AB 123456/123                   | 123456  | 1 | 6 | NULL |
+----+---------------------------------+---------+---+---+------+
| 3  | AB 123456-123                   | 123456  | 1 | 6 | NULL |
+----+---------------------------------+---------+---+---+------+
| 4  | AB B0-23456.123                 | 0       | 1 | 1 | NULL |
+----+---------------------------------+---------+---+---+------+
| 4  | AB B0-23456.123                 | 23456   | 1 | 5 | -    |
+----+---------------------------------+---------+---+---+------+
| 5  | AB 1234 5678 9545 3214.123      | 1234    | 1 | 4 | NULL |
+----+---------------------------------+---------+---+---+------+
| 5  | AB 1234 5678 9545 3214.123      | 5678    | 1 | 4 |      |
+----+---------------------------------+---------+---+---+------+
| 5  | AB 1234 5678 9545 3214.123      | 9545    | 1 | 4 |      |
+----+---------------------------------+---------+---+---+------+
| 5  | AB 1234 5678 9545 3214.123      | 3214    | 1 | 4 |      |
+----+---------------------------------+---------+---+---+------+
| 6  | AB 123456 123                   | 123456  | 1 | 6 | NULL |
+----+---------------------------------+---------+---+---+------+
| 7  | AB.123456 123                   | 123456  | 1 | 6 | NULL |
+----+---------------------------------+---------+---+---+------+
| 8  | AB..123456 123                  | 123456  | 1 | 6 | NULL |
+----+---------------------------------+---------+---+---+------+
| 9  | AB..1C23456 123                 | 1C23456 | 0 | 7 | NULL |
+----+---------------------------------+---------+---+---+------+
| 10 | AB 1234 5678 954 3214-12345.123 | 1234    | 1 | 4 | NULL |
+----+---------------------------------+---------+---+---+------+
| 10 | AB 1234 5678 954 3214-12345.123 | 5678    | 1 | 4 |      |
+----+---------------------------------+---------+---+---+------+
| 10 | AB 1234 5678 954 3214-12345.123 | 954     | 1 | 3 |      |
+----+---------------------------------+---------+---+---+------+
| 10 | AB 1234 5678 954 3214-12345.123 | 3214    | 1 | 4 |      |
+----+---------------------------------+---------+---+---+------+
| 10 | AB 1234 5678 954 3214-12345.123 | 12345   | 1 | 5 | -    |
+----+---------------------------------+---------+---+---+------+

SELECT ID,YourString

返回的列是分组列加上一个大的计算列。

这是一个 相关子查询 FOR XML PATH ,这是连接所有结果的技巧。

最棘手的是 WHERE <4 ,字符串将不包括此片段和以下所有片段。

我怎么才能拿到钱 ?

相关子查询 获取ID组中具有 FragmentIndex 比现在的大。