Is there a SQL function that will split fixed width delimited rows into a table with a row for each value?
I need to split a list of comma separated fixed width strings. In one specific case, I need to split a list of eight character hexadecimal PIN numbers. Other targets are a more efficient feature than the general purpose SQL splitting functions that are rife with SO and the internet, and user-friendly syntax like:
declare @pinList varchar(max)
set @pinList = 'D1D57EFD,9917D94B,0687E581,C6AA229E,044B136B,ED90E4AF,143E23FB,DF5CF1CB,D711F644,67138659'
select *
from fixedWidthSplitFunction(@list, 8, ',')
+3
source to share
1 answer
Here is a special, fixed width, limited row, table splitting function (well, that was 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
Usage example
select *
from tvfFixedWidthSplitter('D1D57EFD,9917D94B,0687E581,C6AA229E,044B136B,ED90E4AF,143E23FB,DF5CF1CB,D711F644,67138659', 8, ',')
Because its domain is a fixed-width, delimited string, it includes optimizations that are not possible in most general-purpose partitioning functions. In my testing, it outperforms all other general purpose string splitting functions I've come across 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
+2
source to share