tsql 函数拆分字符串

2021-09-10 00:00:00 sql tsql sql-server

我想知道是否有人可以帮助我.

I wonder if anyone can help me.

我需要一个 tsql 函数来分割给定的值，例如:

I need a tsql function to split a given value such as:

1)    00 Not specified
3)    01-05 Global WM&BB | Operations
2)    02-05-01 Global WM&BB | Operations | Operations n/a

我需要得到这样的结果:

I need to get a result like this:

cat1  cat1descr       cat2    cat2descr     cat3   cat3descr
----------------------------------------------------------------
00    Not especified  null    null          null   null
01    Global WM&BB    05      Operations    null   null
01    Global WM&BB    05      Operations    01     Operations n/a

结果将始终有 6 列

select funcX('00 未指定');

select funcX('00 Not specified');

cat1  cat1descr      cat2  cat2descr      cat3  cat3descr
----------------------------------------------------------------
00    Not especified  null  null          null   null

推荐答案

这将适用于 SQL Server 2005 和 SQL Server 2008.我假设您的第一个数字序列固定为 1、2、或 3. 您可以使用较少的级联 CTE 来完成此操作，但我发现 SUBSTRING/CHARINDEX/LEN 语法很快就会变得非常难以阅读和调试.

This will work on SQL Server 2005 and SQL Server 2008. I have assumed that your first sequence of digits is fixed to 2-digit groups of 1, 2, or 3. You can do this with fewer cascading CTEs but I find the SUBSTRING/CHARINDEX/LEN syntax can quickly become very difficult to read and debug.

DECLARE @foo TABLE
(
    bar VARCHAR(4000)
);

INSERT @foo(bar) SELECT '00 Not specified'
UNION ALL SELECT '01-05 Global WM&BB | Operations'
UNION ALL SELECT '02-05-01 Global WM&BB | Operations | Operations n/a';


WITH split1 AS
(
    SELECT 
        n = SUBSTRING(bar, 1, CHARINDEX(' ', bar)-1),
        w = SUBSTRING(bar, CHARINDEX(' ', bar)+1, LEN(bar)),
        rn = ROW_NUMBER() OVER (ORDER BY bar)
    FROM
        @foo
),
split2 AS
(
    SELECT
        rn,
        cat1 = LEFT(n, 2),
        wl = RTRIM(SUBSTRING(w, 1, 
             COALESCE(NULLIF(CHARINDEX('|', w), 0)-1, LEN(w)))),
        wr = LTRIM(SUBSTRING(w, NULLIF(CHARINDEX('|', w),0) + 1, LEN(w))),
        cat2 = NULLIF(SUBSTRING(n, 4, 2), ''),
        cat3 = NULLIF(SUBSTRING(n, 7, 2), '')
    FROM
        split1
),
split3 AS
(
    SELECT
        rn,
        cat1descr = wl,
        cat2descr = RTRIM(SUBSTRING(wr, 1, 
              COALESCE(NULLIF(CHARINDEX('|', wr), 0)-1, LEN(wr)))),
        cat3descr = LTRIM(SUBSTRING(wr, 
              NULLIF(CHARINDEX('|', wr),0) + 1, LEN(wr)))
    FROM 
        split2
)
SELECT
    s2.cat1, s3.cat1descr,
    s2.cat2, s3.cat2descr,
    s2.cat3, s3.cat3descr
 FROM split2 AS s2
INNER JOIN split3 AS s3
ON s2.rn = s3.rn;

相关文章