How To Split Delimited Strings in SQL Server?

As SQL Server programmers, we often need to split the string or text, based on delimiters. There are so many ways to split delimited strings. Recently I’ve gone through a simple way to split delimited string written by Clayton in Code Project. That method uses XML and parsed the data for integer, numeric and date-time. My requirement does not need to parse the values. So, I’ve simplified the method, just to split the delimited string and return the result as strings. Here is the simple function which splits the delimited text and returns the result in table format.

Function To Split Delimited Strings

CREATE FUNCTION [fn_SplitString]
  (@SourceString VARCHAR(MAX)
  ,@Seperator VARCHAR(25)=','
  )
 RETURNS @ResultTable
  TABLE(
	[Position] INT IDENTITY(1,1),
	[Value] VARCHAR(MAX)
   )
AS
/**************************************************************
 * Author: Beaulin @ www.MyTecBits.com
 * Description: Function to split the delimited text and
 *		returns the result in table format
 **************************************************************/
BEGIN
	DECLARE @w_xml xml;

	SET @w_xml = N'<root><i>' + replace(@SourceString, @Seperator,'</i><i>') + '</i></root>';

	INSERT INTO @ResultTable
		([Value])
	SELECT 
		[i].value('.', 'VARCHAR(MAX)') AS Value 
	FROM 
		@w_xml.nodes('//root/i') AS [Items]([i]);
	RETURN;
 END;
 GO

SQL Server: Split Delimited Strings SP

Examples

Example With Single Character Delimiter

This example splits the text delimited by a single character delimiter like “,” or “|” or “;” or any other character.

Select * from fn_SplitString('Cat|Dog|Tiger|Lion|Leopard', '|');

Position    Value
--------    ------------
1	     Cat
2	     Dog
3	     Tiger
4	     Lion
5	     Leopard

Example With Multi Character Delimiter

This example splits the text delimited by a single character delimiter like comma with space “, ” or HTML tags like <p>, <li> or any other word or characters.

Select * from fn_SplitString('Cat; Dog; Tiger; Lion; Leapord', '; ');

Position    Value
--------    ------------
1	     Cat
2	     Dog
3	     Tiger
4	     Lion
5	     Leopard

SQL Server: Split Delimited Strings Example

Related Articles

Related Articles & Tools

Leave your thoughts...