sql - 在两个词之间剪切文本

Question

我只想得到这个:

000020004641,000020006307,000020001441

现在我明白了:

000020004641;1003000020006307#000020006307;1003000020001441#000020001441

在我看来，我必须在 ;还有这个 # 但我不知道怎么做。

你知道怎么做吗？

Answer 1

示例查询:

DECLARE @text VARCHAR(150) = '000020004641;1003000020006307#000020006307;1003000020001441#000020001441'

;WITH CTE AS
(
    --initial part
    SELECT 1 AS MyCounter, LEFT(REPLACE(@text, '#', ';'), CHARINDEX(';',REPLACE(@text, '#', ';'))-1) AS MyText, 
        RIGHT(REPLACE(@text, '#', ';'), LEN(REPLACE(@text, '#', ';')) - CHARINDEX(';',REPLACE(@text, '#', ';'))) AS Remainder
    WHERE CHARINDEX(';',REPLACE(@text, '#', ';'))>0
    --recursive part
    UNION ALL
    SELECT MyCounter +1 AS MyCounter, LEFT(Remainder, CHARINDEX(';',Remainder)-1) AS MyText, 
        RIGHT(Remainder, LEN(Remainder) - CHARINDEX(';',Remainder)) AS Remainder
    FROM CTE
    WHERE CHARINDEX(';',Remainder)>0
    UNION ALL
    SELECT MyCounter +1 AS MyCounter, Remainder AS MyText, NULL AS Remainder
    FROM CTE
    WHERE CHARINDEX(';',Remainder)=0
)
SELECT *
FROM CTE

结果:

MyCounter   MyText  Remainder
1   000020004641    1003000020006307;000020006307;1003000020001441;000020001441
2   1003000020006307    000020006307;1003000020001441;000020001441
3   000020006307    1003000020001441;000020001441
4   1003000020001441    000020001441
5   000020001441    NULL

[编辑]

您是 MVC 开发人员。所以，我的下一个建议是使用 Linq:

string aText = @"000020004641;1003000020006307#000020006307;1003000020001441#000020001441";

var qry = aText.Split(new char[]{';','#'}).Select(x=>x);

干杯
马切伊