Home > Enterprise >  need to split the one string value into multiple columns based on different special characters or wi
need to split the one string value into multiple columns based on different special characters or wi

Time:12-04

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


tags status
test Open
_test Open
test_ Open
1200>test> IP
1200>test>234598 completed

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)
  • Related