kaveer
9/19/2017 - 10:13 AM

Function to split ids

select ID  from ngSplitIntoInts('536971,668545,685520,685516,575590,477923,616588,39276,45413,715386,715385,576358,754358', ',')
USE [staging]
GO

/****** Object:  UserDefinedFunction [dbo].[ngSplitIntoInts]    Script Date: 9/19/2017 2:12:31 PM ******/
DROP FUNCTION [dbo].[ngSplitIntoInts]
GO

/****** Object:  UserDefinedFunction [dbo].[ngSplitIntoInts]    Script Date: 9/19/2017 2:12:31 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[ngSplitIntoInts] 
(
	-- Add the parameters for the function here
	@ArrayString varchar(max),
	@SplitChar char 
	
)
RETURNS @Array TABLE
(
	ID varchar(255) Not Null
) 

AS
BEGIN

declare @StartLocation int
set @StartLocation = 0

declare @LeftString varchar(max)
set @LeftString = ltrim(@ArrayString)

while  charIndex(@SplitChar,@LeftString,0) <> 0
begin
	insert @Array(ID)
	 values(substring(@LeftString, 0 ,charIndex(@SplitChar,@LeftString,0)))
	set @LeftString = ltrim(substring(@LeftString, charIndex(@SplitChar,@LeftString,0) + 1 , len(@LeftString)))

end

if len(@LeftString) > 0
begin
	insert @Array(ID)
	 values(rtrim(ltrim(@LeftString)))
end

	RETURN 
END
GO