Split

function splits text with given delimiter into a table

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE FUNCTION [dbo].[Split]
(    
    @RowData NVARCHAR(2000),
    @Delimeter NVARCHAR(5)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    DATA NVARCHAR(10)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1
 
    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
 
    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (DATA)
        SELECT 
            DATA = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
 
        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))
 
        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END
 
    INSERT INTO @RtnValue (DATA)
    SELECT DATA = LTRIM(RTRIM(@RowData))
 
    RETURN
END

Example

SELECT * FROM dbo.Split('10,11,12,12,14,15',',')

Returns

ID          Data
----------- ----------
1           10
2           11
3           12
4           12
5           14
6           15

(6 row(s) affected)