Saturday, 27 July 2013

Split Function - User Defined function in SQL SERVER with multiple split conditions



     In this article we are going to see a split function in SQL SERVER. How the split function works. The first parameter for the function takes the word and second parameter takes the group of condition based on words has to be split  “.,@#$ %^” etc.

For Example: Rajesh,…Is  a,C##Developer
Output:
Rajesh
Is
A
C#
Developer

Let we see the function Now,

CREATE FUNCTION [DBO].[SPLIT]
(
@DATA NVARCHAR(MAX),
@SPLITCONDITION NVARCHAR(30)
)
RETURNS @VALUE TABLE(TEXT NVARCHAR(MAX))
AS
BEGIN
DECLARE @CONLEN INT = LEN (@SPLITCONDITION)

      IF @CONLEN < 1
      BEGIN
            INSERT INTO @VALUE(TEXT) SELECT @DATA
            RETURN
      END
      ELSE
      BEGIN       
            DECLARE @LEN  INT = LEN(@DATA)      
            DECLARE @LOOP INT = 0
            DECLARE @TEMPDATA NVARCHAR(MAX)
            DECLARE @CHAR CHAR(1)
            SELECT @TEMPDATA = ''
                       
            WHILE(@LOOP <= @LEN)
            BEGIN
                  SELECT @CHAR = SUBSTRING(@DATA,@LOOP,1)
                 
                  IF CHARINDEX(@CHAR,@SPLITCONDITION) > 0
                  BEGIN
                  IF  @TEMPDATA <> ''
                   BEGIN
                        INSERT INTO @VALUE(TEXT) SELECT @TEMPDATA
                        SELECT @TEMPDATA = ''
                    END
                  END
                  ELSE 
                  BEGIN 
                        SELECT @TEMPDATA = @TEMPDATA + @CHAR
                  END
                  SELECT @LOOP = @LOOP+1
            END
                  IF @TEMPDATA <>''
                  INSERT INTO @VALUE(TEXT) SELECT @TEMPDATA                   
            RETURN
      END
      RETURN
END

How to call the Split function ?
SELECT TEXT FROM DBO.SPLIT('C#@IS,A,.OBJECT ORIENTED$LANGUAGE.,','.,$@ &')

Output :


TEXT
1
C#
2
IS
3
A
4
OBJECT
5
ORIENTED
6
LANGUAGE


From this article we can see how the user defined split function is created in SQL server and executed.