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)
programming/mssql/split.txt · Last modified: 2018-06-21 19:48 (external edit)
CC Attribution-Noncommercial-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0