Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - Very Fast String Splitter

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        10 Apr 2007

Description

While not strictly data conversion, this piece of code is one that wears many hats (Conversion, Manipulation, Pivoting, Transformation) and this was the first of these categories that I uploaded to the site, so this is where the code ended up.
 
Several times I have looked at this code with the intent of figuring out just how it works.  Time then runs on and I end up like this little guy.  You'll see what I mean when you look at it yourself. 
 
I just know that it works reliably, and very fast for 99% of the strings that I've needed to split.  There is always the occasional odd one - e.g. a multi-character delimiter - that it will not cope with.
 

Code

DROP FUNCTION dbo.udf_split
GO

CREATE FUNCTION dbo.udf_split
(
  
@List VARCHAR(8000),
  
@Delimiter CHAR(1)
)
RETURNS @Results TABLE
      
(
          
Value VARCHAR(8000)
       )
AS

BEGIN

   SET
@List = REPLACE(REPLACE(@List,CHAR(10),''),CHAR(13),'')

  
INSERT @Results
  
SELECT LTRIM(SUBSTRING(@Delimiter  + @List + @Delimiter , w.i + 1, CHARINDEX(@Delimiter , @Delimiter  + @List + @Delimiter , w.i + 1) - w.i - 1)) value
  
FROM
      
(
          
SELECT TOP 100 PERCENT
              
v0.n + v1.n + v2.n + v3.n i
          
FROM
          
(
              
SELECT 0 n    UNION ALL    SELECT 1     UNION ALL
              
SELECT 2      UNION ALL    SELECT 3     UNION ALL
              
SELECT 4      UNION ALL    SELECT 5     UNION ALL
              
SELECT 6      UNION ALL    SELECT 7     UNION
               SELECT
8      UNION ALL    SELECT 9     UNION ALL
              
SELECT 10     UNION ALL    SELECT 11    UNION ALL
              
SELECT 12     UNION ALL    SELECT 13    UNION ALL
              
SELECT 14     UNION ALL    SELECT 15
          
) AS v0,
           (
              
SELECT 0 n    UNION ALL    SELECT 16    UNION ALL
              
SELECT 32     UNION ALL    SELECT 48    UNION ALL
              
SELECT 64     UNION ALL    SELECT 80    UNION ALL
              
SELECT 96     UNION ALL    SELECT 112   UNION
               SELECT
128    UNION ALL    SELECT 144   UNION ALL
              
SELECT 160    UNION ALL    SELECT 176   UNION ALL
              
SELECT 192    UNION ALL    SELECT 208   UNION ALL
              
SELECT 224    UNION ALL    SELECT 240
          
) AS v1,
           (
              
SELECT 0 n    UNION ALL    SELECT 256   UNION ALL
              
SELECT 512    UNION ALL    SELECT 768   UNION ALL
              
SELECT 1024   UNION ALL    SELECT 1280  UNION ALL
              
SELECT 1536   UNION ALL    SELECT 1792  UNION
               SELECT
2048   UNION ALL    SELECT 2304  UNION ALL
              
SELECT 2560   UNION ALL    SELECT 2816  UNION ALL
              
SELECT 3072   UNION ALL    SELECT 3328  UNION ALL
              
SELECT 3584   UNION ALL    SELECT 3840
          
) AS v2,
           (
              
SELECT 0 n    UNION ALL    SELECT 4096
          
) AS v3 ORDER BY i
      
) w
  
WHERE w.i = CHARINDEX(@Delimiter , @Delimiter  + @List + @Delimiter , w.i)
   AND
w.i < LEN(@Delimiter  + @List)

  
RETURN
END

/*
DECLARE @ListVar VARCHAR (100)

SELECT @ListVar = 'this, is,  a, comma,delimited,  list'
SELECT * FROM dbo.udf_split(@ListVar, ',')

*/


ċ
String Splitter - Very fast.sql
(2k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments