How to split comma separate string in Sql server

This is common requirement for almost every developer to extract data from comma separated string. In Sql Server 2016, we have STRING_SPLIT  inbuilt function to extract data from string. But for people those are using prior versions of Sql Server like me 😥 , still have to strugle with this issue. If you Google about this function, you’ll find out more than 10 ways of extrating data from comma separted string. Here, I present my own version 🙂

--SELECT * FROM DBO.StringSplitter('Sunil,Sanjay,Neha,Aradhaya,Nikhil',',')
--SELECT * FROM DBO.StringSplitter('Sunil',',')
CREATE FUNCTION StringSplitter
(
	@STR VARCHAR(100), -- Input string 
	@SEPARATOR VARCHAR(1) -- Separator character 
)
RETURNS @TEMP TABLE (NAME VARCHAR(100)) -- Return data in tabular format 
AS
BEGIN
DECLARE @START INT= 1, @STRLENGTH INT = LEN(@STR);
-- First check whether input string is comma separater string or not.
-- If NOT then insert the whole string into our table variable and 
-- return. 
IF(CHARINDEX(',', @STR, @START) = 0)
 BEGIN
  INSERT INTO @TEMP SELECT @STR
 END
-- If input string is a comma separated string then go in and extract data 
ELSE
 BEGIN
  -- Before loop over the input string first add a ',' (comma)
  -- at the end of the input string because if we don't add
  -- then last comma separated word will be missed.
  SET @STR += ',';
  -- loop till @START variable is smaller than length of the input string
  WHILE(@START < @STRLENGTH)
   BEGIN
        -- get the substring from begining of the input string till it finds a
	-- comma. Once you find a comma set this value to your @START variable
	-- by increasing it's value by 1 because next search should start from
	-- next character after found comma 
	INSERT INTO @TEMP 
	SELECT SUBSTRING(@STR, @START, CHARINDEX(',', @STR, @START) - @START)
	SET @START = CHARINDEX(',', @STR, @START) + 1
   END
END
RETURN;
END
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s