.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Split function for comma separated values in SQL Server

Posted By:Maverick       Posted Date: April 11, 2011    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com  

Split function for comma separated values in SQL server. You can use below SQL server function to Split comma separated value
 

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- This function splits a variable-length parameter array (actually a string
-- with comma as a delimiter) and stored the values into the table.
-- CHARINDEX() function is used to identify the position of the first delimiter
-- in the text and SUBSTRING() function is used to set the 'element' column.

ALTER FUNCTION [dbo].[fnSplit](
    @string    varchar(550) -- '1,2,3'
)
RETURNS @table TABLE(element int)
AS
BEGIN
DECLARE @tmpString varchar(550),
    @delimPos AS tinyint
     
SET @delimPos = 0         
SET @tmpString = LTRIM(RTRIM(@string))
--If you can find the delimiter in the text, retrieve the first element and
--insert it into the temp table.         
WHILE CHARINDEX(',',@tmpString) > 0 
 BEGIN 
 SET @delimPos = CHARINDEX(',',@tmpString)
 INSERT INTO @table(element) VALUES (CAST((LEFT(@tmpString,@delimPos-1)) AS smallint))
  --Trim the element and its delimiter from the front of the string and loop. 
 SET @tmpString = RTRIM(LTRIM(SUBSTRING(@tmpString,@delimPos+1,LEN(@tmpString)-@delimPos))) 
 END 
--If you can't find the delimiter in the text, @String is the last value
INSERT INTO @table(element) VALUES (CAST((@tmpString) AS smallint))

RETURN
END

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend