/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
--- takes a delimetered value and returns a table --Usage: -- SELECT Data -- FROM dbo.fnSplit('A,B,C', ','); CREATE FUNCTION [dbo].[fnSplit] ( @RowData VARCHAR(4000), @SplitOn VARCHAR(5) ) RETURNS @RtnValue TABLE ( Id INT IDENTITY(1,1), DATA VARCHAR(1000) ) AS BEGIN DECLARE @Cnt INT SET @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) BEGIN INSERT INTO @RtnValue (DATA) SELECT DATA = ltrim(rtrim(SUBSTRING(@RowData,1,Charindex(@SplitOn,@RowData)-1))) SET @RowData = SUBSTRING(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) SET @Cnt = @Cnt + 1 END INSERT INTO @RtnValue (DATA) SELECT DATA = ltrim(rtrim(@RowData)) RETURN END