How to select split string from column-CodePudding

I want to select split records from the phone column.

My table looks like that:

customerId      phone                       address      ...
 1              1234567                     qwe          ...
 2              234567,135791,4556457       asd          ...
 3              3425546,34454365            zxc          ...

I need to select each phone number separately with the customerId:

1  1234567
2  234567
2  135791
2  4556457
3  3425546
3  34454365

I don't want to change table and split to different rows, only to select.

Thanks!

CodePudding user response：

Using string_split() in concert with a CROSS APPLY... Use OUTER APPLY if you want to see NULL values

Select A.customerID
      ,Phone = B.value
 From  YourTable A
 Cross Apply string_split(A.Phone,',') B