i have a supplier table that stores supplier email, and the column can store 1 or more email address separated by carriage return character. I need to split the value in multiple rows.
select value from string_split('1,2,3', ',')
this will give you a result with 3 rows,
to join with other tables, you can use cross apply, for example :
select s.id, value as user_email, s.name as user_name from purchase_order po inner join supplier s on po.supplier_id = s.id cross apply string_split(s.email, char(10)) where po.id= 123