SQL Server Split function

          Sometimes we come across data with delimiter. Delimiter be anything , any specific symbol, any character a series of symbol to identify different data. It is obvious that we will process this data, before doing that  , you need to separate the data. 


          As this kind of situation may happen multiple places in a project, database developer need it to create a function related to this.
            The function will receive the total data, and the denominator, then the function will return tables after splitting the data. 

Here is the example how the function can be written and how to use this functions.

Example 1

CREATE FUNCTION dbo.fnSplit

(

    @InputData VARCHAR(8000)

, @Delimiter VARCHAR(8000)

)

RETURNS @List TABLE (element VARCHAR(8000))

BEGIN

       DECLARE @data VARCHAR(8000)

--

         WHILE CHARINDEX(@Delimiter,@InputData,0) <> 0

                      BEGIN

                         SELECT          @data=RTRIM(LTRIM(SUBSTRING(@InputData,1,CHARINDEX(@Delimiter,@InputData,0)-1))),

@InputData=RTRIM(LTRIM(SUBSTRING(@InputData,CHARINDEX(@Delimiter,@InputData,0)+LEN(@Delimiter),LEN(@InputData))))

                            IF LEN(@data) > 0

                                     INSERT INTO @List SELECT @data

          END

              IF LEN(@InputData) > 0

                               INSERT INTO @List SELECT @InputData -- Put the last item in

          RETURN

END

Query

 

select * from dbo.fnSplit('1*2*2*3*4','*')

Output

element

1
2
2
3
4

Example 2

DECLARE @myData VARCHAR(500)

DECLARE @Deliminator VARCHAR(500)

SELECT @myData='DATA1/**/DATA2/**/DATA3/**/DATA4/**/DATA5/**/DATA6/**/DATA7/**/DATA8/**/DATA9/**/DATA10/**/DATA11/**/'

SELECT @Deliminator='/**/'

Query

select element from dbo.fnSplit(@myData,@Deliminator)

Output 

element

DATA1
DATA2
DATA3
DATA4
DATA5
DATA6
DATA7
DATA8
DATA9
DATA10
DATA11