[MS-SQL] 전화번호 자리수에 따른 "-" 붙여주기

CREATE function[dbo].[StringTelnoFormat]

(

       @num varchar(11)    -- 전화번호

)

returns varchar(13)

/****************************************************************

프로그램유형: Function

프로그램ID  :

프로그램명: StringTelnoFormat

내      용: 전화번호자리수에따른"-" 붙여주기

작  성자: 최보현

작  성일: 2010/12/04

수정내용:

------------------------------------------------------------

select dbo.StringTelnoFormat('01032111585')

*****************************************************************/

begin

       declare @retuvarchar(13) 

       set @num=ltrim(rtrim(@num))

 

       set @retu=

             case

                    when left(@num,2)='02'

                           then

                                 case

                                        when len(@num)=9 then left(@num,2)+'-'+substring(@num,3,3)+'-'+substring(@num,6,len(@num))

                                        when len(@num)=10 then left(@num,2)+'-'+substring(@num,3,4)+'-'+substring(@num,7,len(@num))

                                 end

                    when left(@num,2)!='02'

                           then

                                 case

                                        when len(@num)=10 then left(@num,3)+'-'+substring(@num,4,3)+'-'+substring(@num,7,len(@num))

                                        when len(@num)=11 then left(@num,3)+'-'+substring(@num,4,4)+'-'+substring(@num,8,len(@num))

else @num

                                 end

                    else @num

             end   

 

       return @retu

end