Translate: 
EnglishFrenchGermanItalianPolishPortugueseRussianSpanish

Equivalent of the PHP’s explode() function in Microsoft SQL Server 2000/2005

There are situations, when you need to port a functionality from one programming language to another.

Some time ago I had to create the equivalent of explode() function in Microsoft SQL 2005.

Here is the result of my one-day battle with SQL.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[PHP_EXPLODE]
	@INPUT NVARCHAR(MAX),
	@SEPARATOR NVARCHAR(100) = '	'
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @LAST_POSITION BIGINT
	DECLARE @CURRENT_POSITION BIGINT
	DECLARE @CURRENT_VALUE NVARCHAR(MAX)
	DECLARE @SEPARATOR_LENGTH INT
	DECLARE @INPUT_LENGTH BIGINT
	DECLARE @START BIGINT
	DECLARE @ID BIGINT
 
	DECLARE @OUTPUT TABLE (
		ID BIGINT NOT NULL PRIMARY KEY,
		MYVALUE NVARCHAR(MAX) NOT NULL
	)
 
	SET @SEPARATOR_LENGTH = LEN(@SEPARATOR)
	SET @INPUT_LENGTH = LEN(@INPUT)
	SET @LAST_POSITION = -@SEPARATOR_LENGTH;
	SET @ID = 1
	SET @CURRENT_POSITION = CHARINDEX(@SEPARATOR, @INPUT);
	WHILE (@CURRENT_POSITION > 0) 
	BEGIN
		INSERT INTO @OUTPUT VALUES(@ID, (SUBSTRING(@INPUT, @LAST_POSITION + @SEPARATOR_LENGTH, @CURRENT_POSITION - @LAST_POSITION - @SEPARATOR_LENGTH)))
		SET @LAST_POSITION = @CURRENT_POSITION;
		SET @CURRENT_POSITION = CHARINDEX(@SEPARATOR, @INPUT, @LAST_POSITION + 1);
		SET @ID = @ID + 1
	END
 
	IF(@INPUT_LENGTH > @LAST_POSITION)
	BEGIN
		INSERT INTO @OUTPUT VALUES(@ID, (SUBSTRING(@INPUT, @LAST_POSITION + @SEPARATOR_LENGTH, @INPUT_LENGTH - @LAST_POSITION)))	
	END
	SELECT * FROM @OUTPUT
	RETURN 0
END

Sample usage

Query:

EXEC php_explode "hello!!!@world!", "!!!@"

Result:

ID                   MYVALUE
---------------------------------
1                    hello
2                    world

Why so much code?

The biggest problem is that Microsoft does not like overly long strings of data. In SQL Server 2000, the VARCHAR (MAX) variable can store up to 8,000 characters, which is by today’s standards a ridiculously small size.

Also, most of the SQL procedures that operate on strings can not cope with very long text values. However, problem should not exists in case of the above procedure (though I do not recommend to use it with old versions of SQL Server).

In addition, performance tests shows that the T-SQL is not the best solution to implement the explode() functionality. In case of few thousand words to explode, you can achieve much better results by using the C#/C++/PHP equivalents.

3 Responses to “Equivalent of the PHP’s explode() function in Microsoft SQL Server 2000/2005”

  1. Ian Cowley says:

    SQL Server has been able to cope with string over 8000 chars since SQL2005.
    Here is a SQL function which will cope with 1 Million characters, it could do more if required, with a small change. I may well also give PHPs Explode command a run for it’s money on performance too. But I’ve not tested that.

    CREATE FUNCTION [dbo].[Explode] (@Input NVARCHAR(MAX), @Separator NVARCHAR(100))
    RETURNS TABLE AS
    RETURN
    WITH
    E1(i) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
    E2(i) AS (SELECT 1 FROM E1 a, E1 b),
    E6(i) AS (SELECT 1 FROM E2 a, E2 b, E2 c),
    Possibles(i) AS (SELECT 0 UNION ALL SELECT TOP (LEN(@Input) - LEN(@Separator)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E6),
    Matches(i) AS (SELECT CASE p.i WHEN 0 THEN p.i + 1 ELSE p.i + LEN(@Separator) END FROM Possibles p WHERE (SUBSTRING(@Input, p.i, LEN(@Separator)) = @Separator OR p.i = 0))

    SELECT
    ItemNumber = ROW_NUMBER() OVER(ORDER BY m.i),
    Item = SUBSTRING(@Input, m.i, ISNULL(NULLIF(CHARINDEX(@Separator, @Input, m.i), 0) - m.i, 1000000))
    FROM Matches m;
    GO

  2. Artur Graniszewski says:

    Hi Ian,

    thanks for posting this SQL. Looks great to me. My solution is slower and more complicated because I needed it to run on MS SQL 2000

  3. Jed Ecker says:

    Hi Ian,

    I was wondering if you had any restrictions for usage of the code you have posted. It fits a need I have very well, but I want to be sure there isn’t an issue with my using it.

    Thanks!

Leave a Reply