Convert comma separated string values into integer values

Declare @ApiId varchar(max)

set @ApiId='10,20,30,40,50,60'

select * from [dbo].[fn_IntegerSplit](@ApiId,',')

-- in below select query Id is of Integer datatype

select value from [dbo].[fn_IntegerSplit](@ApiId,',')

Example:

value

10

20

30

40

50

60

GO

/****** Object:  UserDefinedFunction [dbo].[fn_IntegerSplit]    Script Date: 02/26/2019 12:27:37 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_IntegerSplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_IntegerSplit]

GO

/*

Declare @ApiId varchar(max)

set @ApiId='10,20,30,40,50,60'

select * from [dbo].[fn_IntegerSplit](@ApiId,',')

*/

Create FUNCTION [dbo].[fn_IntegerSplit]    

(    

 @List nvarchar(max),    

 @Separator nvarchar(5)    

)      

RETURNS @tmpTable table     

(    

      

 Id int identity(1,1),    

 Value nvarchar(100)    

)     

AS      

BEGIN    

    

While (Charindex(@Separator,@List)>0)    

Begin     

 Insert Into @tmpTable (value)    

 Select ltrim(rtrim(Substring(@List,1,Charindex(@Separator,@List)-1)))     

     

    Set @List = Substring(@List,Charindex(@Separator,@List)+len(@Separator),len(@List))    

End     

    Insert Into @tmpTable (Value)    

    Select ltrim(rtrim(@List))    

    delete from @tmpTable where Value = ''    

    Return    

END

GO