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)