Converting CSV or fixed-width lists to a table

The functions here are from http://www.sommarskog.se/arrays-in-sql.html. That article goes into great length on the various ways of doing this, their performance implications, etc.

In general, if you just need to use a list of values in an IN clause and the number of values is in the hundreds (or fewer), the fastest method is to build the dynamic SQL in application code.  However, sometimes you need to JOIN against such a list as a table or to just import thousands of comma-separated values into a table without having to resort to creating a file for Bulk Insert.

For these scenarios you can use these user-defined functions (UDFs), which, according to that article, should be the fastest way of doing this.

First you'll need a table of integers.  You need enough integers to cover the number of elements in the list, however, a table of integers is useful for a variety of situations, so the SQL in this file will create a table called integerSequence with a single int column named i and populate it with integers from 1 to 1 million:

integerSequence-create.sql

Next, these two files will create UDFs called CsvToTable and FixedWidthToTable:

CsvToTable.sql
FixedWidthToTable.sql

Note that CsvToTable returns a table with varchar and nvarchar columns, whereas FixedWidthToTable returns a table with 1-based list position column and varchar column.

FixedWidthToTable is faster than CsvToTable and is convenient when you need to import a list of values that are all the same length, such as a list of 10-digit phone numbers.  Unlike CsvToTable, FixedWidthToTable is actually pretty short:

   CREATE FUNCTION dbo.FixedWidthToTable(@str text, @itemlen tinyint)
   RETURNS TABLE
   AS
   RETURN(SELECT listpos = n.i,
                 str = substring(@str, @itemlen * (n.i - 1) + 1, @itemlen)
          FROM   integerSequence n
          WHERE  n.i <= datalength(@str) / @itemlen +
                 CASE datalength(@str) % @itemlen WHEN 0 THEN 0 ELSE 1 END)