Tuesday, January 4, 2011

Compress CSV String

 

For presentation purposes on reports, i often need to show the data in CSV. The CSV string are normally very similar and for long string of CSV, it become very unsightly. So i wrote a small function to compress the CSV string. From “0001,0002,0004,0005’ to “0001,2,4,5”.

create function dbo.fn_compress_csv
(
@csv_list nvarchar(max)
)
returns nvarchar(max)
with encryption
as
begin
declare @com_len int

; with list as
(
select item = stringval, max_len = max(len(stringval)) over()
from dbo.CSVTable(@csv_list)
)
select @com_len = max(n.NUMBER)
from (
select n.NUMBER
from list l
cross apply dbo.F_TABLE_NUMBER_RANGE (1, l.max_len) n
group by n.NUMBER
having min(substring(item, 1, n.NUMBER)) = max(substring(item, 1, n.NUMBER))
) n

return stuff(replace(',' + @csv_list, ',' + left(@csv_list, @com_len), ','), 1, 1, left(@csv_list, @com_len))end




In that compress_csv function, i utilize 2 other function CSVTable and F_TABLE_NUMBER_RANGE. You can reference it from the link.

select    dbo.fn_compress_csv('00012,00013,00014')

this will give result : 00012,3,4