Geek Answers Handbook

Tsql function separator string

I wonder if anyone can help me.

I need a tsql function to split a given value, for example:

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 this result:

 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

The result will always have 6 columns.

select funcX ('00 Not specified ');

 cat1 cat1descr cat2 cat2descr cat3 cat3descr ---------------------------------------------------------------- 00 Not especified null null null null
+3
source share
3 answers

This will work on SQL Server 2005 and SQL Server 2008. I assumed that your first digit sequence is mapped to 2-digit groups of 1, 2, or 3. You can do this with fewer cascading CTEs, but I find the syntax SUBSTRING / CHARINDEX / LEN 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;
+6
source

You can do this using PatIndex and SubString

0
source

If @In is the value of the string you are trying to parse, try the following:

  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
0
source

More articles:


All Articles