Feeds:
Posts
Comments

Posts Tagged ‘Split function in sql server’

We use split function in our development frequently. The purpose of this function is to split single string having delimiters (comma, semicolon etc.) to convert into multiple strings. Many developers have designed it in different ways. 
Given below is the split function, developed with the help of XQuery.
Let me explain this function demonstrating simple example.

Split the @variable:

--Create this user deinfed function.
Create Function dbo.[UDF_Split] 
(           
      @String VARCHAR(MAX),  -- Variable for string
      @delimiter VARCHAR(50) -- Delimiter in the string 
)
RETURNS @Table TABLE(        --Return type of the function
Splitcolumn VARCHAR(MAX)
) 
BEGIN
	 Declare @Xml AS XML  
-- Replace the delimiter to the opeing and closing tag 
--to make it an xml document
	 SET @Xml = cast(('<A>'+replace(@String,@delimiter,'</A><A>')+'</A>') AS XML)  
--Query this xml document via xquery to split rows 
--and insert it into table to return.
	 INSERT INTO @Table SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)  
RETURN
END
GO
-- For Example :
SELECT * FROM dbo.[UDF_Split] ('Bob,Hulda,Jacoba',',')
-- We can use this function to split table's column as well 
--but the issue is, if we have 5 rows in a table
--This function will be executed 5 times.
--I have another solution to split table's column
GO

Split the Table’s Column:

CREATE TABLE [dbo].[Student](
      [Student ID]   [varchar] (6) NOT NULL ,
      [Student Name] [varchar](50) NOT NULL,
      [Code] [varchar] (MAX))
GO
Insert into dbo.[Student] values ('STD001','Bob','E1,E2,E3')
Insert into dbo.[Student] values ('STD002','Alexander','X1,X2,X3')
Insert into dbo.[Student] values ('STD003','Hosanna','A1,A2,A3')
GO
-- Here is the sample query to split table's column.
Declare @delimiter VARCHAR(50)
Set @delimiter=','
;WITH Cte AS 
( 
    SELECT 
        [Student ID], 
	    [Student Name],
        -- Replace the delimiter to the opeing and closing tag 
		--to make it an xml document
        CAST('<M>' + REPLACE([Code], @delimiter , '</M><M>') + '</M>' AS XML) AS [Code]
    FROM  [Student] 
)
Select 
	[Student ID], 
	[Student Name],
	--Query this xml document via xquery to split rows 
	Split.a.value('.', 'VARCHAR(MAX)') AS [Code] 
FROM Cte 
CROSS APPLY [Code].nodes('/M')Split(a)

I’d really appreciate your comments on my posts. Please feel free to comment.

Read Full Post »