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
EXEC php_explode "hello!!!@world!", "!!!@"
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.