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

Tuesday, October 5, 2010

Replace Function

The standard replace() function will replace all occurrence of the string. It will not be possible to replace just the 1st or 2nd occurrence of the string. I have written a replacement for replace() function to do that.

create function fn_replace
(
    @searched    varchar(max),
    @find        varchar(max),
    @replace    varchar(max),
    @nth        int        -- 0 : replace all, non zero : nth occurrance
)
returns    varchar(max)
as
begin
    declare    @output    varchar(max),
        @pos    int,
        @n    int
 
    select    @n = 1
 
    select    @pos = charindex(@find, @searched)
    select    @output = convert(varchar(10), @pos)
 
    while     @n     < @nth
    and    @nth    <> 0
    and    @pos     > 0
    begin
        select    @pos = charindex(@find, @searched, @pos + 1)
        select    @n = @n + 1
    end
 
     select    @output    = case     when @nth = 0    -- replace all
                then replace(@searched, @find, @replace)
                when @pos <> 0
                then stuff(@searched, @pos, len(@find), @replace)
                else @searched
                end
 
    return (@output)
end

Friday, October 1, 2010

Number Table

Number table or tally table as it is also known as, is a very useful table in my day to day query. There are lots of way to do that. In my other post, i uses a numbers table. Here is how i create the numbers table
create table numbers 
(
    num     int, 
    primary key (num)
)


And to populate it with numbers, a recursive cte is used.


; with num as
(
    select    n = 0
    union all
    select    n = n + 1
    from    num
    where    n     < 1000
)
insert into numbers (num)
select    n
from    num option (maxrecursion 0)

Here i inserted number 0 to 1000. You can change the query to any number you required

Generate Number Combination

Prompted by a friend on an interesting thread today in SQLTeam. OP ask for help to generate a number combination result with input like ‘X123’, the result expected will be 10 records

0123
1123
2123
3123
4123
5123
6123
7123
8123
9123



Basically the idea is to replace X with number 0 to 9. This can be easily done by cross join it to a number table and replace the X with the number.


The twist is for input like ‘X1X3’, the result is not just 10 records by 100 records. Like 0103, 1103, 2103, . . . 0113, 0123, 0133 etc


To achieve this, what came to my mind is to use derived table or CTE and cross join to a numbers table (tally table) 2 times. (see Number Table on how to create one)


And this is what i came up with



declare    @input_num char(4)
 
select    @input_num = '1X3X'
 
; with 
cte as
(
    select    input_num = @input_num
),
cte1 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte c
        outer apply
        (
            select    input_num = stuff(input_num, 1, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 1, 1) = 'X'
        ) n
),
cte2 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte1 c
        outer apply
        (
            select    input_num = stuff(input_num, 2, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 2, 1) = 'X'
        ) n
),
cte3 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte2 c
        outer apply
        (
            select    input_num = stuff(input_num, 3, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 3, 1) = 'X'
        ) n
),
cte4 as
(
    select    input_num = isnull(n.input_num, c.input_num)
    from    cte3 c
        outer apply
        (
            select    input_num = stuff(input_num, 4, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, 4, 1) = 'X'
        ) n
)
select    *
from    cte4



and the result for 1X3X is




1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1130
1131
1132



But the above method is restricted to max 4 numbers. I want to make it scalable, so recursive CTE came to my mind.




declare    @input_num varchar(10)
 
select    @input_num = 'X1X3'
 
; with 
cte as
(
    select    input_num = @input_num, pos = 0
 
    union all
 
    select    input_num = convert(varchar(10), isnull(n.input_num, c.input_num)), pos = pos + 1
    from    cte c
        outer apply
        (
            select    input_num = stuff(input_num, c.pos + 1, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
            and    substring(input_num, c.pos + 1, 1) = 'X'
        ) n
    where    pos    < len(@input_num)
)
select    *
from    cte
where    pos    = len(@input_num)
order by input_num

With that, it is able to generate longer numbers. Of-course longer number will need more time to compute and the query will run longer.


But again, i wasn’t very happy with that query. It is looping every numbers. So i thought of using charindex to find the position of X and with that, i can change from OUTER to CROSS APPLY as i do not cater for non X char. And the query is also much simplify.



declare    @input_num varchar(10)
 
select    @input_num = 'X2X4X'
 
; with 
cte as
(
    select    input_num = @input_num, pos = charindex('X', @input_num)
 
    union all
 
    select    input_num = convert(varchar(10), n.input_num),
        pos       = charindex('X', n.input_num)
    from    cte c
        cross apply
        (
            select    input_num = stuff(input_num, c.pos, 1, n.num)
            from    numbers n    
            where    n.num between 0 and 9
        ) n
    where    pos    <> 0
)
select    input_num
from    cte
where    pos    = 0
order by input_num

Oh also, the first thing when i saw the thread is, OP want to buy 4 D numbers Winking smile

Wednesday, June 9, 2010

Sum of Digits of a number

Using recursive cte, you can calculate the sum of all the digits of a number easily.
Example, a number 1234, the sum of the digits will be 1 + 2 + 3 + 4 = 10

Here is the function to do that

create function fn_sumdigit 
(
    @number    int
)
returns    int
as
begin
    declare    @sum    int
 
    ;with cte
    as
    (
        select    number    = @number / 10, digit = @number % 10
    
        union all
    
        select    number = number / 10, digit = number % 10
        from    cte
        where    number    > 0
    )
    select    @sum = sum(digit)
    from    cte
 
    return    @sum
end
go
 
-- Example
select    dbo.fn_sumdigit (12345)

this function will make the calculating the EAN check digit much simpler