FUN_SPLIT

 

----------------1

DECLARE @test VARCHAR(100) = '100,105,494,515,'

SELECT *
FROM dbo.[FUN_SPLIT](@test,',')

 

 

----------------2

 

 

USE [gms2]
GO
/****** Object: UserDefinedFunction [dbo].[FUN_SPLIT] Script Date: 02/05/2015 14:42:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[FUN_SPLIT]
(
@SourceSql nvarchar(4000),
@StrSeprate varchar(10)
)
RETURNS @temp TABLE(Temp_Field nvarchar(2000))

AS
BEGIN
declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql))
set @SourceSql = substring(@SourceSql, 1, len(@SourceSql) - 1)
set @i = charindex(@StrSeprate, @SourceSql)
while @i >= 1
begin
insert @temp values(left(@SourceSql, @i - 1))
set @SourceSql = substring(@SourceSql, @i + 1, len(@SourceSql) - @i)
set @i = charindex(@StrSeprate, @SourceSql)
end
if @SourceSql <> '\'
insert @temp values(@SourceSql)
RETURN
END