----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[fnSplit]
(@pString nvarchar(max),@pSplitChar char(1))
returns @tblTemp table (tid int,value varchar(1000))
as
begin
	declare @vStartPosition		int
	declare @vSplitPosition		int
	declare @vSplitValue		varchar(1000)
	declare @vCounter		int
	set @vCounter=1
	select @vStartPosition = 1,@vSplitPosition=0
	

	set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
	if (@vSplitPosition=0 and len(@pString) != 0)
	begin
		INSERT INTO @tblTemp
			(
			tid		,
			value	
			)
		VALUES
			(
			1	,
			@pString		
			)
		return		--------------------------------------------------------------->>
	end
	set @pString=@pString+@pSplitChar
	while (@vSplitPosition > 0 )
	begin
		set @vSplitValue = substring( @pString , @vStartPosition , @vSplitPosition - @vStartPosition )
		set @vSplitValue = ltrim(rtrim(@vSplitValue))
		INSERT INTO @tblTemp
			(
			tid		,
			value	
			)
		VALUES
			(
			@vCounter	,
			@vSplitValue		
			)
		set @vCounter=@vCounter+1
		set @vStartPosition = @vSplitPosition + 1
		set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )
	end
	return
end

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here