Click here to Skip to main content
Click here to Skip to main content

Split function in SQL

, 30 Jul 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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)

Share

About the Author

Gauri Chodanker
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
SuggestionThis is not a Stored Procedure..it is a Function... PinprofessionalRaja Sekhar S30-Jul-13 21:47 
GeneralMy vote of 4 PinmemberSanthosh Kumar Jayaraman30-Jul-13 20:35 
Suggestionduplicate PinprofessionalBrian A Stephens30-Jul-13 4:53 
GeneralRe: duplicate PinmemberJulian Goldsmith30-Jul-13 5:39 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150224.1 | Last Updated 30 Jul 2013
Article Copyright 2013 by Gauri Chodanker
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid