How to split the column base on the underscore

Question

****Original          Column    After separated****
CustomerID  CustomerID1 CustomerID2 CustomerID3 CustomerID4
1_2_R_0           1             2           R      0
3_3_0             3             3           0    
4_2               4             2

Customer ID is the original column from this I need to split into multiple column base on the underscore to customerID1, customerID2, customerID3 and customerID4.

enter image description here

Tag your DB. For oracle, refer this https://stackoverflow.com/questions/5199849/split-varchar-into-separate-columns-in-oracle — BlackSwan, Apr 23 '19 at 13:48
It can be many customer but the scenario above is the distinct customerID — Maqbul, Apr 23 '19 at 13:58
`STRING_SPLIT` could have made it simpler for you, but it's not supported in 2008. — Amir Molaei, Apr 23 '19 at 14:04
Possible duplicate of [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — UnhandledExcepSean, Apr 23 '19 at 14:17
There will be thousand of customers but number of underscore(_) for each customer can be three or two or one only. — Maqbul, Apr 23 '19 at 14:26

John Cappelletti · Answer 1 · 2019-04-23T15:46:08.143

2

Since there is a max of only 4 IDs, you could use parsename(). However, I would opt for the XML approach.

Example

Select A.CustomerID
      ,B.*
 From  YourTable A
 Cross Apply (
    Select CustomerID1 = xDim.value('/x[1]','varchar(100)')
          ,CustomerID2 = xDim.value('/x[2]','varchar(100)')
          ,CustomerID3 = xDim.value('/x[3]','varchar(100)')
          ,CustomerID4 = xDim.value('/x[4]','varchar(100)')
     From  (values (cast('<x>' + replace([CustomerID],'_','</x><x>')+'</x>' as xml))) X(xDim)
 ) B

Returns

edited Apr 23 '19 at 15:46

answered Apr 23 '19 at 14:36

John Cappelletti

79,615
7
44
66

Hi John, Its working fine exactly as i needed. Thank you very much. – Maqbul Apr 24 '19 at 05:27
1

@Maqbul If this solution is working as you expect then accept this ans. – Kiran Desai Apr 24 '19 at 05:56

How to split the column base on the underscore

1 Answers1