Hi Friends,
Split\convert comma sepatate int values string to table,
Example: '1,2,3,4'
Result:
1
2
3
4
If possible, without using any functions.
--split wihtout using sql function
--1st approach using master database spt_values table
Declare @SplitVal varchar(100)
Set @SplitVal= '1,2,3,4'
Select SUBSTRING(',' + @SplitVal + ',', n.Number + 1, CHARINDEX(',', ',' + @SplitVal + ',', n.Number + 1) - n.Number - 1)
From master..spt_values As n
WHERE n.Type = 'p'
AND n.Number > 0
AND n.Number < LEN(',' + @SplitVal + ',')
and SUBSTRING(',' + @SplitVal + ',', n.Number, 1)=','
GO
--2nd approach using dynamic query
DECLARE @SplitVal AS NVARCHAR(100)
SET @SplitVal=N'1,2,3,4'
SET @SplitVal = REPLACE('SELECT ''' + @SplitVal,',',''' UNION ALL SELECT ''') + ''''
PRINT @SplitVal
EXEC sp_executesql @SplitVal
tham khảo Địa chỉ này
