Function takes 3 arguments:
String, Field Delimitter, and Row Delimitter and returns a table.
This
SELECT * FROM dbo.udf_Split2('This is,aTest|of the, Split2 function','|',',')
Return this:
ID Value1 Vaue2
1 This is a Test
2 of the Split2 function
Function takes 3 arguments:
String, Field Delimitter, and Row Delimitter and returns a table.
This
SELECT * FROM dbo.udf_Split2('This is,aTest|of the, Split2 function','|',',')
Return this:
ID Value1 Vaue2
1 This is a Test
2 of the Split2 function
CREATE FUNCTION [dbo].[udf_Split2] ( @List VARCHAR(8000), @Delimitter VARCHAR(5), @SplitOn VARCHAR(5) ) RETURNS @RtnValue TABLE ( ID INT identity(1,1), Value1 VARCHAR(100), Value2 VARCHAR(100) ) AS BEGIN DECLARE @SubString as VARCHAR(50) DECLARE @Index1 AS INT DECLARE @Index2 AS INT WHILE (Charindex(@SplitOn,@List)>0) BEGIN SELECT @Index1 = Charindex(@Delimitter,@List) If @Index1 = 0 Set @Index1 = 200 SELECT @SubString = ltrim(rtrim(Substring(@List,1,@Index1-1))) SELECT @Index2 = Charindex(@SplitOn,@Substring) INSERT INTO @RtnValue (Value1,Value2) SELECT Value1 = ltrim(rtrim(Substring(@SubString,1,@Index2-1))) , Value2 = ltrim(rtrim(Substring(@SubString,@Index2+1,Len(@Substring)-@Index2))) SET @List = Substring(@List,@Index1 +len(@SplitOn),len(@List)) END RETURN END