Last active
March 7, 2023 21:47
-
-
Save gfody/9443629f893ba4577524 to your computer and use it in GitHub Desktop.
handy table-valued functions for sql server
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment