한글을 Alphabet으로 변환

ALTER FUNCTION [dbo].[fn_HangulToAlphabet]

(

    @StrHangul VARCHAR(64)

) 

RETURNS VARCHAR(256)

AS 

BEGIN   

DECLARE @tblChoJungJong TABLE(

idx INT,

cho VARCHAR(2),

jung VARCHAR(2),

jong VARCHAR(2)

)

/*

// ㄱ ㄲ ㄴ ㄷ ㄸ ㄹ ㅁ ㅂ ㅃ ㅅ ㅆ ㅇ ㅈ ㅉ ㅊ ㅋ ㅌ ㅍ ㅎ

static char *Alpha_ChoSung[] = { "r", "R", "s", "e", "E", "f", "a", "q", "Q", "t", "T", "d", "w", "W", "c", "z", "x", "v", "g" };

// ㅏ ㅐ ㅑ ㅒ ㅓ ㅔ ㅕ ㅖ ㅗ ㅘ ㅙ ㅚ ㅛ ㅜ ㅝ ㅞ ㅟ ㅠ ㅡ ㅢ ㅣ

static char *Alpha_JungSung[] = { "k", "o", "i", "O", "j", "p", "u", "P", "h", "hk", "ho", "hl", "y", "n", "nj", "np", "nl", "b", "m", "ml", "l" };

// ㄱ ㄲ ㄳ ㄴ ㄵ ㄶ ㄷ ㄹ ㄺ ㄻ ㄼ ㄽ ㄾ ㄿ ㅀ ㅁ ㅂ ㅄ ㅅ ㅆ ㅇ ㅈ ㅊ ㅋ ㅌ ㅍ ㅎ

static char *Alpha_JongSung[] = { "", "r", "R", "rt", "s", "sw", "sg", "e", "f", "fr", "fa", "fq", "ft", "fx", "fv", "fg", "a", "q", "qt", "t", "T", "o", "w", "c", "z", "x", "v", "g" };

*/

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(0, 'r', 'k', '')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(1, 'R', 'o', 'r')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(2, 's', 'i', 'R')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(3, 'e', 'O', 'rt')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(4, 'E', 'j', 's')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(5, 'f', 'p', 'sw')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(6, 'a', 'u', 'sg')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(7, 'q', 'P', 'e')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(8, 'Q', 'h', 'f')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(9, 't', 'hk', 'fr')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(10, 'T', 'ho', 'fa')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(11, 'd', 'hl', 'fq')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(12, 'w', 'y', 'ft')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(13, 'W', 'n', 'fx')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(14, 'c', 'nj', 'fv')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(15, 'z', 'np', 'fg')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(16, 'x', 'nl', 'a')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(17, 'v', 'b', 'q')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(18, 'g', 'm', 'qt')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(19, '', 'ml', 't')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(20, '', 'l', 'T')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(21, '', '', 'o')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(22, '', '', 'w')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(23, '', '', 'c')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(24, '', '', 'z')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(25, '', '', 'x')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(26, '', '', 'v')

INSERT INTO @tblChoJungJong(idx, cho, jung, jong) VALUES(27, '', '', 'g')

DECLARE @position int, @nstring nvarchar(30), @resultStr varchar(256);

DECLARE @ch int, @cho int, @jung int, @jong int

SET @position = 1;

SET @nstring = convert(nvarchar, @StrHangul)

SET @resultStr = ''

WHILE @position <= DATALENGTH(@nstring)

BEGIN

set @ch = UNICODE(SUBSTRING(@nstring, @position, 1));

IF @ch >= 0x3131 and @ch <= 0x314E

BEGIN

-- 초성만

select

@resultStr = @resultStr + (select top 1 cho from @tblChoJungJong where idx = (@ch - 0x3131))

END

ELSE IF @ch >= 0x314F and @ch <= 0x3163

BEGIN

-- 중성만

select

@resultStr = @resultStr + (select top 1 jung from @tblChoJungJong where idx = (@ch - 0x314F))

END

ELSE IF @ch >= 0xAC00 and @ch <= 0xD7A3

BEGIN

-- 한글

set @ch = @ch - 0xAC00

set @cho = @ch / (21 * 28);

set @ch = @ch % (21 * 28);

set @jung = @ch / 28;

set @jong = @ch % 28

select

@resultStr = @resultStr + (select top 1 cho from @tblChoJungJong where idx = @cho) + (select top 1 jung from @tblChoJungJong where idx = @jung) + (select top 1 jong from @tblChoJungJong where idx = @jong)

END

ELSE IF @ch is not NULL

BEGIN

-- 한글 이외

select

@resultStr = @resultStr + SUBSTRING(@nstring, @position, 1)

END

/*

ELSE IF @ch is NULL

BEGIN

-- 값 없음

select @position, '값 없음'

END

*/

SELECT @position = @position + 1;

END;

RETURN @resultStr

END