|
-- 한글인 경우, 자음 추출
-- 한글이 아닌 경우 그대로 출력
CREATE FUNCTION Parse_Str(@STR VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @I INT, @J INT, @TMPSTR VARCHAR(255), @COL1 VARCHAR(2)
SET @I = LEN(@STR)
SET @J = 1
SET @TMPSTR = ''
WHILE (@J<=@I)
BEGIN
SELECT @COL1 = SUBSTRING(@STR, @J, 1)
SET @TMPSTR = @TMPSTR +
CASE WHEN @COL1 < 'ㄱ' THEN @COL1
WHEN ascii('ㄱ') <= ascii(@COL1) and
ascii(@COL1)<= ascii('ㅎ') THEN @COL1
WHEN @COL1 < '까' THEN 'ㄱ'
WHEN @COL1 < '나' THEN 'ㄲ'
WHEN @COL1 < '다' THEN 'ㄴ'
WHEN @COL1 < '따' THEN 'ㄷ'
WHEN @COL1 < '라' THEN 'ㄸ'
WHEN @COL1 < '마' THEN 'ㄹ'
WHEN @COL1 < '바' THEN 'ㅁ'
WHEN @COL1 < '빠' THEN 'ㅂ'
WHEN @COL1 < '사' THEN 'ㅃ'
WHEN @COL1 < '싸' THEN 'ㅅ'
WHEN @COL1 < '아' THEN 'ㅆ'
WHEN @COL1 < '자' THEN 'ㅇ'
WHEN @COL1 < '짜' THEN 'ㅈ'
WHEN @COL1 < '차' THEN 'ㅉ'
WHEN @COL1 < '카' THEN 'ㅊ'
WHEN @COL1 < '타' THEN 'ㅋ'
WHEN @COL1 < '파' THEN 'ㅌ'
WHEN @COL1 < '하' THEN 'ㅍ'
WHEN @COL1 <= '힣' THEN 'ㅎ'
ELSE @COL1
END
SET @J = @J + 1
END
RETURN(@TMPSTR)
END
GO
CREATE TABLE [good_table] (
[good_cd] [varchar] (13) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[good_knm] [varchar] (100) COLLATE Korean_Wansung_CI_AS NULL,
[good_knm_sep] varchar(255) NULL -- 자음 추출한 데이터가 저장
) ON [PRIMARY]
GO
CREATE INDEX IDX__knm_sep ON good_table(good_knm_sep)
GO
CREATE TRIGGER TRG_GOOD_TABLE
ON GOOD_TABLE
FOR INSERT, UPDATE
AS
BEGIN
UPDATE A
SET good_knm_sep = dbo.Parse_Str(B.good_knm)
FROM good_table A JOIN Inserted B ON A.good_cd = B.good_cd
END
GO
--예) '삼성파브' 입력시
INSERT GOOD_TABLE(good_cd, good_knm) VALUES ('12345','삼성파브')
GO
SELECT * FROM GOOD_TABLE
/*
good_cd good_knm good_knm_sep
-----------------------------------------
12345 삼성파브 ㅅㅅㅍㅂ
(1개 행 적용됨)
*/ | 
HeidiSQL_9.2_Portable.zip