숫자 세자리마다 콤마(,) 넣는 프로시저 > db

-- @number : 넘겨받은 숫자, 콤마(,)를 넣어야 하는 숫자
-- @celingcheck : 넘겨받은 숫자의 소수점 이하 버릴것인지 여부

create function Comma(@number varchar(30), @celingcheck char(1))
returns varchar(35)
as
begin
     declare @sNum varchar(30)
     declare @eNum varchar(10)

    if(CHARINDEX('.', @number)>0)
        begin
            set @sNum =  left(@number, CHARINDEX('.', @number)-1)
            if(@celingcheck='Y')
                set @eNum = ''
            else
                 set @eNum = substring(@number, CHARINDEX('.', @number), len(@number)-(CHARINDEX('.', @number)-1))
        end
    else
        begin
            set @sNum = @number
            set @eNum = ''
        end

        declare @numLength int,  @Cnt int
        declare @remainNum varchar(30), @splitNum varchar(10), @newNum varchar(30)
        set @newNum = ''
        set @numLength =  len(@sNum)
        if((@numLength%3)=0)
                set @Cnt = (@numLength/3)-1
        else
                set @Cnt = (@numLength/3)   -- 콤마(,)가 들어갈 횟수
       
        set @remainNum = @sNum 
     declare @outNumber varchar(35)
        if(@Cnt>0)
                begin
                        while(@Cnt>0)
                        begin
                                set @splitNum = right(@remainNum,3)            
                                set @remainNum = left(@remainNum, len(@remainNum)-3)
                                set @newNum =  ',' + @splitNum + @newNum                               
                                set @Cnt = @Cnt - 1
                        end
                        set @splitNum = right(@remainNum,3)            
                       set @outNumber = @splitNum + @newNum
                end
        else
                set @outNumber = @number

     set @outNumber = @outNumber + @eNum

     return(@outNumber)
end

 

-- 사용방법

select dbo.Comma(10000000.123, 'Y')  -- 소수점 이하 자리는 버린다.
select dbo.Comma(10000000, 'Y')