I have one string column. I need to split into multiple columns. The data is like below

I need to split the above into multiple columns as like below

Deptno,empname,Empno

My desired output like below

----------------  ------------ -------   -------- ------
tags              |    status | EmpNO | EmpName  |DepNo|
---------------   |-----------|------ |---------- ------
|test             |   Open    | NULL  |test      | NULL |
|_test            |   Open    | NULL  |test      | NULL |
|test_            |   Open    | NULL  |test      | NULL |
|1200>test>       |   IP      | NULL  |test      | 1200 |
|1200>test>234598 |  completed| 234598|test      | 1200 |

I have written query and able to split the data into multiple columns. But it is not handling all scenario's.

Query:

SELECT [tags],substring([tags], 1 , CHARINDEX('>', [tags])-1) as 'Division',
SUBSTRING([tags],CHARINDEX('>', [tags])   1,LEN([tags]) - CHARINDEX('>', [tags]) - CHARINDEX('-', REVERSE([tags])) ),
REVERSE(SUBSTRING(REVERSE([tags]), 1 , CHARINDEX('>', REVERSE([tags]))-1)) AS 'EmpNo' 
FROM ods.[testtable]

Could someone help me the query.

CodePudding user response：

According to the desired result a query would be like:

SELECT RIGHT(tags, CASE 
        WHEN p2.pos = 0
            THEN 0
        ELSE LEN(tags) - p2.pos
        END) AS EmpNO
,SUBSTRING(tags, CASE 
        WHEN p1.pos = 0
            THEN p1.pos
        ELSE p1.pos   1
        END, CASE 
        WHEN p2.pos = 0
            THEN LEN(tags)   1
        ELSE P2.Pos - P1.Pos - 1
        END) AS EmpName
,LEFT(tags, CASE 
        WHEN p1.pos = 0
            THEN 0
        ELSE p1.pos - 1
        END) AS DepNo
,*
FROM ods.[testtable]
CROSS APPLY (
SELECT (charindex('>', tags))
) AS P1(Pos)
CROSS APPLY (
SELECT (charindex('>', tags, P1.Pos   1))
) AS P2(Pos)

I used this post to create the query Post

CodePudding user response：

Using your sample data the follow would appear to give your desired results:

select tags, status,
    Empno.v EmpNo,
    Replace(Replace(Replace(Replace(tags,'_',''),IsNull(Depno.v,''),''),isnull(Empno.v ,''),''),'>','') EmpName,
    Depno.v Depno
from t
cross apply (values(CharIndex('>', tags)-1))v1(t1)
cross apply (values(CharIndex('>', Reverse(tags))-1))v2(t2)
cross apply (values(Left(tags,NullIf(t1,-1))))Depno(v)
cross apply (values(right(tags,Iif(t2>0,t2,null))))Empno(v)