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)