Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Tip/Trick

Split function in SQL

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
30 Jul 2013CPOL 45K   2   4
SQL function in SQL

Introduction

The snippet below is a small version of all other solutions available. This would help the beginners to easily understand what the code block does.

Description

The working of the stored procedure is as below:

The sp will accept two parameters, one the concatenated string and second the delimiter character. The sp will then loop and extract the substrings until the last delimiter character is parsed. The extracted substrings will be added into a table which will be the output of the sp. To extract the last substring where there are no more delimiters left, an additional if block has been added into the loop. This will check if any other more delimiters exists in the string to be parsed. If no then the last string left will also be added into the table result.

Code Snippet

SQL
ALTER FUNCTION FNC_SPLIT(@MYSTR VARCHAR(500), @DELIMITER CHAR(1))
RETURNS @MYTBL  TABLE (idx smallint, value varchar(8000))
AS 
BEGIN
 DECLARE @RET VARCHAR(500)
 DECLARE @INDEX INT
 DECLARE @COUNTER smallint
 
 --Get the first position of delimiter in the main string
 SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 SET @COUNTER = 0
 
 --Loop if delimiter exists in the main string
 WHILE @INDEX > 0
 BEGIN
  --extract the result substring before the delimiter found
  SET @RET = SUBSTRING(@MYSTR,1, @INDEX-1 )
  --set mainstring right part after the delimiter found
  SET @MYSTR = SUBSTRING(@MYSTR,@INDEX+1 , LEN(@MYSTR) - @INDEX )
  --increase the counter
  SET @COUNTER = @COUNTER  + 1 
  --add the result substring to the table
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @RET)
  --Get the next position of delimiter in the main string
  SET @INDEX = CHARINDEX(@DELIMITER,@MYSTR)
 END
 
 --if no delimiter is found then simply add the mainstring to the table
 IF @INDEX = 0 
 BEGIN
  SET @COUNTER = @COUNTER  + 1
  INSERT INTO @MYTBL (idx, value)
  VALUES (@COUNTER, @MYSTR)
 END 
 RETURN   
END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
SuggestionThis is not a Stored Procedure..it is a Function... Pin
Raja Sekhar S30-Jul-13 20:47
Raja Sekhar S30-Jul-13 20:47 
GeneralMy vote of 4 Pin
Santhosh Kumar Jayaraman30-Jul-13 19:35
Santhosh Kumar Jayaraman30-Jul-13 19:35 
Suggestionduplicate Pin
Brian A Stephens30-Jul-13 3:53
professionalBrian A Stephens30-Jul-13 3:53 
GeneralRe: duplicate Pin
Julian Goldsmith30-Jul-13 4:39
Julian Goldsmith30-Jul-13 4:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.