숫자를 한글로 변환 함수

2017. 2. 8. 17:15

/****** Object: UserDefinedFunction [dbo].[fnMoneyString] Script Date: 2017-02-08 오후 4:52:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*****************************************************************************************
내용 : 숫자를 한글로 변환해주는 함수
작성 :
날짜 : 2017-02-08

수정 : 0000-00-00 홍길동 > 수정내용
*****************************************************************************************/

ALTER FUNCTION [dbo].[fnMoneyString]
(
@WON BIGINT
)
RETURNS VARCHAR(200)
AS
BEGIN

DECLARE
@INPUT_VALUE VARCHAR(50)
, @NOW_POSITION TINYINT
, @ACC_KOREAN_VALUE NVARCHAR(100)
, @NOW_POSITION_VALUE CHAR(1)
, @BUFFER_KOREAN_VALUE NVARCHAR(100)
, @SIGN NVARCHAR(50)

SELECT @INPUT_VALUE = CAST(@WON AS VARCHAR(50)), @NOW_POSITION = 0, @ACC_KOREAN_VALUE = '', @NOW_POSITION_VALUE =''

SET @SIGN = CASE WHEN @WON = 0 THEN '영' WHEN @WON > 0 THEN '' ELSE '마이너스' END

WHILE (1=1)
BEGIN
SELECT @NOW_POSITION = LEN(@INPUT_VALUE), @NOW_POSITION_VALUE = LEFT(@INPUT_VALUE, 1)

IF LEN(@INPUT_VALUE) = 0 BREAK

  IF @NOW_POSITION_VALUE <> 0
  BEGIN
   SELECT
    @ACC_KOREAN_VALUE =
    @ACC_KOREAN_VALUE +
    CASE @NOW_POSITION_VALUE
     WHEN '0' THEN '영'
     WHEN '1' THEN '일'
     WHEN '2' THEN '이'
     WHEN '3' THEN '삼'
     WHEN '4' THEN '사'
     WHEN '5' THEN '오'
     WHEN '6' THEN '육'
     WHEN '7' THEN '칠'
     WHEN '8' THEN '팔'
     WHEN '9' THEN '구'
    END +
    CASE @NOW_POSITION
     WHEN '1' THEN ''
     WHEN '2' THEN '십'
     WHEN '3' THEN '백'
     WHEN '4' THEN '천'
     WHEN '5' THEN '만'
     WHEN '6' THEN '십만'
     WHEN '7' THEN '백만'
     WHEN '8' THEN '천만'
     WHEN '9' THEN '억'
     WHEN '10' THEN '십억'
     WHEN '11' THEN '백억'
     WHEN '12' THEN '천억'
     WHEN '13' THEN '조'
     WHEN '14' THEN '십조'
     WHEN '15' THEN '백조'
     WHEN '16' THEN '천조'
     WHEN '17' THEN '경'
     WHEN '18' THEN '십경'
     WHEN '19' THEN '백경'
     WHEN '20' THEN '천경'
    END

   IF CHARINDEX ('만',@ACC_KOREAN_VALUE) > 0
   BEGIN
    WHILE (1=1)
    BEGIN
     IF ( CHARINDEX ('만',@ACC_KOREAN_VALUE) > 0 )
      SELECT @BUFFER_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('만', @ACC_KOREAN_VALUE),1,'')

      IF ( CHARINDEX ('만',@BUFFER_KOREAN_VALUE) = 0 )
      BEGIN
       BREAK;
      END
      ELSE
      BEGIN
       SELECT @ACC_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('만', @ACC_KOREAN_VALUE),1,'')
      END
    END
   END

   IF CHARINDEX ('억',@ACC_KOREAN_VALUE) > 0 BEGIN
    WHILE (1=1)
    BEGIN
     IF ( CHARINDEX ('억',@ACC_KOREAN_VALUE) > 0 )
      SELECT @BUFFER_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('억', @ACC_KOREAN_VALUE),1,'')

      IF ( CHARINDEX ('억',@BUFFER_KOREAN_VALUE) = 0 )
      BEGIN
       BREAK;
      END
      ELSE
      BEGIN
       SELECT @ACC_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('억', @ACC_KOREAN_VALUE),1,'')
      END
    END
   END

   IF CHARINDEX ('조',@ACC_KOREAN_VALUE) > 0
   BEGIN
    WHILE (1=1)
    BEGIN
     IF ( CHARINDEX ('조',@ACC_KOREAN_VALUE) > 0 )
      SELECT @BUFFER_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('조', @ACC_KOREAN_VALUE),1,'')

      IF ( CHARINDEX ('조',@BUFFER_KOREAN_VALUE) = 0 )
      BEGIN
       BREAK;
      END
      ELSE
      BEGIN
       SELECT @ACC_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('조', @ACC_KOREAN_VALUE),1,'')
      END
    END
   END

   IF CHARINDEX ('경',@ACC_KOREAN_VALUE) > 0
   BEGIN
    WHILE (1=1)
    BEGIN
     IF ( CHARINDEX ('경',@ACC_KOREAN_VALUE) > 0 )
      SELECT @BUFFER_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('경', @ACC_KOREAN_VALUE),1,'')

      IF ( CHARINDEX ('경',@BUFFER_KOREAN_VALUE) = 0 )
      BEGIN
       BREAK;
      END
      ELSE
      BEGIN
       SELECT @ACC_KOREAN_VALUE = STUFF(@ACC_KOREAN_VALUE, CHARINDEX('경', @ACC_KOREAN_VALUE),1,'')
      END
    END
   END
  END

SELECT @INPUT_VALUE = SUBSTRING(@INPUT_VALUE,2,LEN(@INPUT_VALUE)-1)
END

RETURN @SIGN + @ACC_KOREAN_VALUE;

END

저작자표시

'IT > MSSQL' 카테고리의 다른 글

pivot column names change (0)	2017.03.03
column을 row로 row를 column으로 변환 (0)	2017.02.15
부서같은 하이라키 구조의 정보 추출 쿼리 (0)	2017.01.26
MSSQL 캐시 확인 및 삭제 (2)	2016.04.06
테이블 칼럼 등록 및 type, description 정보 추출 쿼리 (0)	2015.12.23