.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 >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

SPLIT function with custom delimiter for SQL SERVER.

Posted By:shiv chandra       Posted Date: June 12, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

SPLIT string with delimiter you specified. Example of table valued function.
 

In almost every project it is required to split comma separated string in to multiple rows. Sometimes it is fixed that data is separated by comma but in some cases delimiter is something other than comma. So I have created one function that will split data by delimiter you specified. So it is not required to use comma only.

Split function has two parameters.

First one is data with delimiter (i.e. comma) and

Second one is delimiter through which you want to split data.

/* This function Separates data and return datatable with column named 'item' having type varchar.
** Note that here you can specify delimiter */
CREATE FUNCTION dbo.Split(
    @StringToSeparate VARCHAR(8000),	-- List of items that neeed to Separate
    @Delimiter VARCHAR(8000) = ','		-- Delimiter that separates items from string
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
  DECLARE @Item VARCHAR(8000)
	
   --Loop through string untill specified delimiter is found
   WHILE CHARINDEX(@Delimiter,@StringToSeparate,0) <> 0
   BEGIN
       --Select the data
	SELECT   @Item=RTRIM(LTRIM(SUBSTRING(@StringToSeparate,1,CHARINDEX(@Delimiter,@StringToSeparate,0)-1)),
@StringToSeparate=RTRIM(LTRIM(SUBSTRING(@StringToSeparate,CHARINDEX(@Delimiter,@StringToSeparate,0)+LEN(@Delimiter),LEN(@StringToSeparate)))) -- For remove leading and traiing space we have use LTRIM and RTRIM function IF LEN(@Item) > 0 -- If data is found insert in to table, that is going to return INSERT INTO @List SELECT @Item END IF LEN(@StringToSeparate) > 0 -- Insert last item in table. INSERT INTO @List SELECT @StringToSeparate RETURN END GO
Normal Example:
select * from Split('1,22,333,', ',')
Output:



Other Example:
--Convert return value as integer
select CAST(item as int) as IntegerItem from Split('1,22,333,4444,', ',')
Output:


Third Example:
--Using different Delimiter, note that here '$'is embadded in string
select * from Split('String1$$String2$String2$$String3','$$')

Output:


Suggestions are most welcome.

Regards,
Shiva


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

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