SQL Server Split

 Some times we meet situation were we pass multiple value ,separeted with deliminator.We need to get the data and get the result in column wise.

 For example i am passing a parameter to a stored procedure which contain 5 student id,I need to get all id in a column for processing

 create procedure pr_school

 (

 @in_student VARCHAR(MAX)

 )

 EXEC pr_school '1,2,3,4,5'

In this situation we need to split the student record , we need a function

----------------------------------------------------------------

CREATE FUNCTION [dbo].[fn_GetSplit]

( 

    @sInputList VARCHAR(8000) -- List of delimited items 

  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items 

) 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 -- Put the last item in 

RETURN 

END

 ---------------------------------------------------------------

Example 1:

 SELECT item  FROM dbo.fn_GetSplit('India,Russia,japan',',')

 Result :

 item

 India

 Russia

 japan

 here , deliminator is comma(,)

 Example 2:

 SELECT item  FROM dbo.fn_GetSplit('India/==/Russia/==/japan','/==/')

 Result :

 item

 India

Russia

japan

here , deliminator is some character ('/==/')