0

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!

Dale K
  • 25,246
  • 15
  • 42
  • 71
rotem
  • 115
  • 1
  • 8
  • 2
    Comma separated items... always causing problems. – jarlh Oct 11 '21 at 17:30
  • `STRING_SPLIT` - look it up, should get you what you need. – Andrew Oct 11 '21 at 17:33
  • Does this answer your question? [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) particularly this answer https://stackoverflow.com/questions/10914576/t-sql-split-string – Charlieface Oct 11 '21 at 18:48

1 Answers1

1

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66