[mssql]전화번호(xxx-xxxx-xxxx) 에서 앞, 중간, 뒷자리 구하는 function

create function fn_substring
(
  @t_val varchar(100),
  @idx int,
  @col_tp int
)
returns varchar(100)
as
begin
  declare @rtn_val varchar(100)
 
  if @col_tp = 1 --전화번호
    begin
      if @idx = 1
        begin
          select @rtn_val=
            CASE
                WHEN CHARINDEX('-', @t_val, 0) > 0 AND CHARINDEX('-', @t_val, CHARINDEX('-', @t_val, 0)+1) > 0 THEN
                  SUBSTRING(@t_val, 1, CHARINDEX('-', @t_val, 0)-1)
                ELSE
                  SUBSTRING(REPLACE(@t_val, '-', ''), 1, 3)
            END
         end
      if @idx = 2
        begin
          select @rtn_val=
            CASE
                WHEN CHARINDEX('-', @t_val, 0) > 0 AND CHARINDEX('-', @t_val, CHARINDEX('-', @t_val, 0)+1) > 0 THEN
                  SUBSTRING(@t_val, CHARINDEX('-', @t_val, 0)+1, CHARINDEX('-', @t_val, CHARINDEX('-', @t_val, 0)+1)-CHARINDEX('-', @t_val, 0)-1)
                ELSE
                  SUBSTRING(REPLACE(@t_val, '-', ''), 4, 4)
            END
        end
      if @idx = 3
        begin
          select @rtn_val=
            CASE
                WHEN CHARINDEX('-', @t_val, 0) > 0 AND CHARINDEX('-', @t_val, CHARINDEX('-', @t_val, 0)+1) > 0 THEN
                  SUBSTRING(@t_val, CHARINDEX('-', @t_val, CHARINDEX('-', @t_val, 0)+1)+1, LEN(@t_val))
                ELSE
                  SUBSTRING(REPLACE(@t_val, '-', ''), 8, 4)
            END
        end          
    end
    
  return @rtn_val

end

;