handy table-valued functions for sql server

csv.sql

-- returns (row, col, value) table for specified csv, rows and cols are 1-based
create function string_split_csv(@string varchar(max), @field_separator char(1), @row_separator char(1)) returns table as
return
	select		a.row, b.col, b.s value
	from		(select row_number() over (order by (select 1)) row, * from string_split(@string, @row_separator)) a
	cross apply	(select row_number() over (order by (select 1)), iif(left(value, 1) in (char(10), char(13)), substring(value, 2, len(value) - 1), value)
	from		string_split(a.value, @field_separator)) b(col, s)

create function string_split_csv_quoted(@string varchar(max), @field_separator char(1), @row_separator char(1), @text_qualifier char(1)) returns table as
return
	select		a.row, b.col, iif(left(b.s, 1) = @text_qualifier and right(b.s, 1) = @text_qualifier, substring(b.s, 2, len(b.s) - 2), b.s) value
	from		(select row_number() over (order by (select 1)) row, * from string_split_quoted(@string, @row_separator, @text_qualifier)) a
	cross apply	(select row_number() over (order by (select 1)), iif(left(value, 1) in (char(10), char(13)), substring(value, 2, len(value) - 1), value)
	from		string_split_quoted(a.value, @field_separator, @text_qualifier)) b(col, s)

-- split string to table, specify option (maxrecursion 0)
create function string_split(@string varchar(max), @separator char(1)) returns table as return
with s(lag_n, n) as (
	select convert(bigint, null), charindex(@separator, @string) union all
	select nullif(n, 0), coalesce(nullif(charindex(@separator, @string, n + 1), 0), len(@string) + 1)
	from s where n <= len(@string))
select iif(n > 0, substring(@string, coalesce(lag_n + 1, 1), n - coalesce(lag_n + 1, 1)), '') value from s where n > 0

create function string_split_quoted(@string varchar(max), @separator char(1), @qualifier char(1)) returns table as return
with
f(q, s) as (select
	coalesce(nullif(charindex(@qualifier, @string), 0), len(@string) + 1),
	coalesce(nullif(charindex(@separator, @string), 0), len(@string) + 1)),
r(q, quoted, last_q, last_s) as (
	select iif(q < s, 1, 0), iif(q < s, 1, 0), q, s from f union all
	select iif(next_q < next_s, 1, 0), iif(next_q < next_s, quoted ^ 1, quoted), next_q, next_s
	from r cross apply (select
		next_q = iif(last_q <= last_s, coalesce(nullif(charindex(@qualifier, @string, last_q + 1), 0), len(@string) + 1), last_q),
		next_s = iif(last_s <= last_q, coalesce(nullif(charindex(@separator, @string, last_s + 1), 0), len(@string) + 1), last_s)) x
	where last_q <= len(@string) or last_s <= len(@string)),
d(l, r) as (
	select coalesce(lag(last_s) over (order by (select 1)), 0) + 1, last_s from r where q=0 and quoted=0)
select substring(@string, l, r - l) value from d where @string is not null

csv_to_table.sql

-- dynamically calls string_split_csv, pivots to flat table, selects into @table_name or just selects if @table_name is null
create procedure csv_to_table(@csv varchar(max), @field_separator char(1) = ',', @row_separator char(1) = 0x0A, @text_qualifier char(1) = null, @has_header_row bit = 1, @table_name sysname = null) as
begin
	declare @sql nvarchar(max) = 'select ', @cols varchar(max) = ''
	select
		@sql += concat('[', col, '] as ', iif(@has_header_row = 1 and ltrim(rtrim(value)) <> '', quotename(value), '[(No column name)]'), ', '),
		@cols += concat('[', col, '],')
	from string_split_csv_quoted(@csv, @field_separator, @row_separator, @text_qualifier) where row=1 option (maxrecursion 0)

	select @sql = substring(@sql, 1, len(@sql) - 1), @cols = substring(@cols, 1, len(@cols) - 1) where len(@cols) > 0

	set @sql += concat(' into ' + @table_name, ' from string_split_csv', iif(@text_qualifier is null, '', '_quoted'),
		'(@csv, @field_separator, @row_separator', iif(@text_qualifier is null, '', ', @text_qualifier'),
		') pivot (max(value) for col in (', @cols, ')) x', iif(@has_header_row = 1, ' where row > 1', ''),
		' option (maxrecursion 0)')

	exec sp_executesql @sql, N'@csv varchar(max), @field_separator char(1), @row_separator char(1), @text_qualifier char(1)',
		@csv=@csv, @field_separator=@field_separator, @row_separator=@row_separator, @text_qualifier=@text_qualifier
end

dates_between.sql

-- returns each month-1st between two dates inclusive
create function months_between(@start date, @end date) returns table as return
select dateadd(month, value, dateadd(day, 1, eomonth(@start, -1))) value from numbers(0, datediff(month, @start, @end))

-- returns every day between two dates inclusive
create function days_between(@start date, @end date) returns table as return
select dateadd(day, value, @start) value from numbers(0, datediff(day, @start, @end))

-- returns datetime for every hour interval between two datetimes inclusive, truncated to the hour
create function hours_between(@start datetime, @end datetime) returns table as return
select dateadd(hour, value, dateadd(ms, -(datepart(minute, @start) * 60000 + datepart(s, @start) * 1000 + datepart(ms, @start)), @start)) value
from numbers(0, datediff(hour, @start, @end))

-- returns datetime for every minute interval between two datetimes inclusive, truncated to the minute
create function minutes_between(@start datetime, @end datetime) returns table as return
select dateadd(minute, value, dateadd(ms, -(datepart(s, @start) * 1000 + datepart(ms, @start)), @start)) value
from numbers(0, datediff(minute, @start, @end))


-- returns numbers from @lo to @hi inclusive, @hi - @lo mustn't exceed 16,777,216
create function numbers(@lo int, @hi int) returns table as return
with a(n) as (select top (@hi - @lo + 1) n from (values
	(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
	(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) x(n))
select top (@hi - @lo + 1) row_number() over (order by (select 1)) - (1 - @lo) value
from a a left join a b on (@hi - @lo) >= 64 left join a c on (@hi - @lo) >= 4096 left join a d on (@hi - @lo) >= 262144