Is there a SQL function that will split fixed-width, delimited strings into a table with a row for each value?

Question

I need to be able to split a comma-separated list of fixed-width strings. In one specific case I need to split a list of 8 - character hexadecimal PIN numbers. Other goals are a higher-performing function than the general-purpose SQL splitting functions that abound on SO and the Internet, and a user-friendly syntax such as:

declare @pinList varchar(max)
set @pinList = 'D1D57EFD,9917D94B,0687E581,C6AA229E,044B136B,ED90E4AF,143E23FB,DF5CF1CB,D711F644,67138659'

select *
from fixedWidthSplitFunction(@list, 8, ',')

Answer 1

Here is a special-purpose, fixed-width, delimited string, table-valued splitting function (OK, that was a mouthful):

create function tvfFixedWidthSplitter
(
    @stringList varchar(max),
    @fixedWidth int,
    @delimiter varchar(10)
)
returns @strings table
(
    id int,
    string varchar(max)
)
as

begin

    with buckets as
    (   
        select 1 id

        union all

        select t.id + 1
        from buckets t
        where id = t.id 
            and t.id < len(@stringList)/(@fixedWidth+len(@delimiter))+1
    )

    insert into @strings
    select 
        id, 
        substring(@stringList, ((id - 1) * (@fixedWidth + len(@delimiter)) + (case when len(@delimiter)-1 = 0 then len(@delimiter) else len(@delimiter)-(len(@delimiter)-1) end)), @fixedWidth) string
    from buckets    
    option (maxrecursion 0)

    return;

end

Sample Usage

select *
from tvfFixedWidthSplitter('D1D57EFD,9917D94B,0687E581,C6AA229E,044B136B,ED90E4AF,143E23FB,DF5CF1CB,D711F644,67138659', 8, ',')

Because its domain is fixed-width, delimited strings, it includes optimizations not possible in most general-purpose splitting functions. In my testing it outperforms all other general-purpose string splitting functions that I have encountered when the function is used in a JOIN with physical tables, e.g.:

select u.UserName
from Users u
join tvfFixedWidthSplitter(@pinList, 8, ',') s
    on u.Pin = s.string