Saturday, April 6, 2013

Split function in Sql Server

Sometime we need to do similar kind of task many time. Split  in sql server is one of this type of activity I need very often in almost every project.

We have some of the standard functions defined under tools schema in database. Split is one of this kind of function we defined under tools schema.


                                             
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split](
@sInputList VARCHAR(8000)
, @sDelimiter VARCHAR(1) = ','
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList
RETURN
END

Post a Comment