tsql function split string

I wonder if anyone can help me.

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

R开发者_如何学运维esult will have always 6 columns

select funcX('00 Not specified');

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

---

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;

---

You can do this using PatIndex and SubString

---

If @In is the value of the string you're trying to parse, then try this:

   Select 
    Case When firstDash = 0 Then zz.sKey 
         Else left(zz.sKey, FirstDash-1) End cat1,
      Ltrim(RTrim(Case When firstPipe = 0 Then zz.Vals
         Else Left(zz.Vals, firstPipe -1) End)) ca1Desc,
    Case When firstDash = 0 Then Null
         When secondDash = 0 
            Then SubString(zz.sKey, FirstDash+1, Len(zz.skey))
       Else SubString(zz.sKey, FirstDash+1, secondDash-firstDash-1) End cat2,
      Ltrim(RTrim(Case When firstPipe = 0 Then Null
         When secondPipe = 0 
            Then SubString(zz.Vals, firstPipe+1, Len(zz.Vals))
       Else SubString(zz.Vals, firstPipe+1, 
                      secondPipe-firstPipe-1) End)) cat2Desc,
    Case When secondDash > 0 
         Then Substring(zz.sKey, secondDash+1, len(sKey)-seconddash) End cat3,
            Ltrim(RTrim(Case When secondPipe > 0 
                Then Substring(zz.Vals, secondPipe+1, 
                     len(Vals)-secondPipe) End)) cat3Desc
   From (Select Z.sKey, Z.Vals,
           charIndex('-', Z.skey) firstDash,
           charIndex('-', Z.skey, 1 + charIndex('-', Z.skey)) secondDash,
           charIndex('|', Z.Vals) firstPipe,
        charIndex('|', Z.Vals, 1 + charIndex('|', Z.Vals)) secondPipe
      From  (Select Left(@In, CharIndex(' ', @In)-1) skey,
              substring(@In, CharIndex(' ', @In)+ 1, Len(@In)) vals) Z) ZZ