s

Home > mssql > mssql comma separated string to rows

mssql comma separated string to rows edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 11 March 2021 | 1474

using mssql query how can I convert a comma-separated string into individual rows. I have a comma separated string of countries "Afghanistan, Albania, Algeria, American Samoa, Andorra, Angola, Anguilla, Antarctica, Antigua And Barbuda, Argentina". I would like to convert this into rows of countries.

Solution Variable To Rows

You have a variable @countries with value comma separated countries. Below code converts the comma separated list of countries into rows of country. Below sample is from a variable to list or rows

declare @countries nvarchar(512) = 'Afghanistan, Albania, Algeria, American Samoa, Andorra, Angola, Anguilla, Antarctica, Antigua And Barbuda, Argentina'
select value as country from STRING_SPLIT(@countries , ',')

Solution Table Column To Rows

Below code creates a temporary table #Country and insert the coma separated list of countries into into the database. Below code converts the comma separated countries into rows of country. Below sample is from a Table column to list or rows

drop table IF EXISTS #Country
create table #Country
(
	countries nvarchar(512) NULL
)

insert into #Country(countries) values('Afghanistan, Albania, Algeria, American Samoa')
insert into #Country(countries) values('Andorra, Angola, Anguilla, Antarctica, Antigua And Barbuda, Argentina')


SELECT Value as Country
FROM #Country 
    CROSS APPLY STRING_SPLIT(countries , ',')