STRING_SPLIT (Transact-SQL) is avail able from SQL Server 2016
1. This is single paged query to under standing split string.
- Declare @products varchar(200) = '1,2,3,4,5,6,7,8,9,10'
- -- SET @products = SUBSTRING(@products, LEN('ss' + ',') + 1, LEN(@products))
- --select @products
- Declare @individual varchar(20) = null
- WHILE LEN(@products) > 0
- BEGIN
- IF PATINDEX('%,%',@products) > 0
- BEGIN
- SET @individual = SUBSTRING(@products, 0, PATINDEX('%,%',@products))
- SELECT @individual
- SET @products = SUBSTRING(@products, LEN(@individual + ',') + 1,
- LEN(@products)-1)
- END
- ELSE
- BEGIN
- SET @individual = @products
- SET @products = NULL
- SELECT @individual
- END
- END
- select @products