CODEWITHSUNDEEP
sqlsql-serversplit
German Varanytsya
German Varanytsya

Reputation: 397

how to split column by commas using MS SQL

this is my code

select top 100
       ih.streetname,
       ih.district,
       ih.intervalhouse
from vw_pn_intervalshouse ih
inner join vw_pn_street s on s.id = ih.streetname
CROSS APPLY STRING_SPLIT(ih.intervalhouse, ',');

i am trying to use string_split but result is incorrect

i will show you some output

district        streetname      intervalhouse
32000100001832  1008302496093   1,17
32000100001832  1008302496093   1,17
32000100001832  1008302496095   8,10,12
32000100001832  1008302496095   8,10,12
32000100001832  1008302496095   8,10,12

result must be like

district        streetname      intervalhouse
32000100001832  1008302496093   1
32000100001832  1008302496093   17
32000100001832  1008302496095   8
32000100001832  1008302496095   10
32000100001832  1008302496095   12

what could be the problem? any solutions?

Upvotes: 0

Views: 58

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

You need to select the value column from string_split. You are selecting the column in your table. 

select top 100
       ih.streetname,
       ih.district,
       s.value
from vw_pn_intervalshouse ih
inner join vw_pn_street s on s.id = ih.streetname
CROSS APPLY STRING_SPLIT(ih.intervalhouse, ',') s;

Upvotes: 5

Related Questions