Following query is used to Split the Comma separated string into individual values.
This query can also be used to split any special character separated string.
Declare @P_text varchar(1000)
Set @P_text='1,2,3,4,5,6,7,8,9,10'
--select Charindex(',',@P_text)
--select
PATINDEX('%,%',@P_text)
Declare @table as Table(Id Int)
Declare @x1 Varchar(100)
While CHARINDEX(',',@P_text) > 1
Begin
set @x1=SUBSTRING(@P_text,0,PATINDEX('%,%',@P_text))
Set @P_text=SUBSTRING(@P_text,PATINDEX('%,%',@P_text)+1,LEN(@P_text))
Insert into
@table(Id)
select cast(@x1 as int)
End
Insert into
@table
Select cast(@P_text as int)
select * from @table
