読者です 読者をやめる 読者になる 読者になる

SQLServerでの文字列の半角文字から全角文字への変換

SQLServer T-SQL

データの全角半角変換、、 - QA@ITじゃんぬねっとさんが言及している関数を実装してみました。

#この掲示板の議論で、じゃんぬねっとさんはtypoしてますねw
#やはり、人間はtypoから逃れられないのかw

※超重要:私もtypoしてました。コメントでご指摘いただきました。ありがとうございます。m(_ _)m レビューせずにコピペしてこのソースを流用した方は、修正をお願いします。m(_ _)m

CREATE FUNCTION dbo.ToWide(@s VARCHAR(512))
	RETURNS VARCHAR(512) AS
BEGIN
	/* ASCII文字はコード順に処理する */
	SET @s = REPLACE(@s COLLATE Japanese_BIN, ' ', ' ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '!', '!')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '"', '”')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '#', '#')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '$', '$')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '%', '%')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '&', '&')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '''', '’')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '(', '(')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, ')', ')')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '*', '*')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '+', '+')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, ',', ',')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '-', '−')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '.', '.')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '/', '/')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '0', '0')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '1', '1')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '2', '2')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '3', '3')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '4', '4')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '5', '5')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '6', '6')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '7', '7')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '8', '8')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '9', '9')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, ':', ':')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, ';', ';')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '<', '<')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '>', '>')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '?', '?')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '@', '@')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'A', 'A')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'B', 'B')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'C', 'C')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'D', 'D')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'E', 'E')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'F', 'F')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'G', 'G')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'H', 'H')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'I', 'I')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'J', 'J')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'K', 'K')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'L', 'L')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'M', 'M')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'N', 'N')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'O', 'O')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'P', 'P')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'Q', 'Q')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'R', 'R')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'S', 'S')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'T', 'T')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'U', 'U')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'V', 'V')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'W', 'W')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'X', 'X')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'Y', 'Y')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'Z', 'Z')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '`', '`')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'a', 'a')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'b', 'b')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'c', 'c')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'd', 'd')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'e', 'e')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'f', 'f')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'g', 'g')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'h', 'h')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'i', 'i')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'j', 'j')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'k', 'k')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'l', 'l')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'm', 'm')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'n', 'n')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'o', 'o')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'p', 'p')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'q', 'q')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'r', 'r')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 's', 's')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 't', 't')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'u', 'u')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'v', 'v')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'w', 'w')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'x', 'x')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'y', 'y')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'z', 'z')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '{', '{')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '|', '|')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '}', '}')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '~', ' ̄')

	/* 濁点・半濁点を先に処理する */
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ガ', 'ガ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ギ', 'ギ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'グ', 'グ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ゲ', 'ゲ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ゴ', 'ゴ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ザ', 'ザ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ジ', 'ジ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ズ', 'ズ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ゼ', 'ゼ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ゾ', 'ゾ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ダ', 'ダ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヂ', 'ヂ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヅ', 'ヅ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'デ', 'デ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ド', 'ド')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'バ', 'バ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ビ', 'ビ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ブ', 'ブ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ベ', 'ベ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ボ', 'ボ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'パ', 'パ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ピ', 'ピ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'プ', 'プ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ペ', 'ペ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ポ', 'ポ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヴ', 'ヴ')
	/* 残りはコード順に処理する */
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '。', '。')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '「', '「')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '」', '」')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '、', '、')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヲ', 'ヲ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ァ', 'ァ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ィ', 'ィ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ゥ', 'ゥ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ェ', 'ェ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ォ', 'ォ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ャ', 'ャ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ュ', 'ュ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ョ', 'ョ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ッ', 'ッ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ー', 'ー')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ア', 'ア')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'イ', 'イ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ウ', 'ウ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'エ', 'エ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'オ', 'オ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'カ', 'カ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'キ', 'キ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ク', 'ク')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ケ', 'ケ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'コ', 'コ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'サ', 'サ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'シ', 'シ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ス', 'ス')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'セ', 'セ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ソ', 'ソ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'タ', 'タ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'チ', 'チ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ツ', 'ツ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'テ', 'テ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ト', 'ト')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ナ', 'ナ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ニ', 'ニ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヌ', 'ヌ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ネ', 'ネ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ノ', 'ノ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ハ', 'ハ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヒ', 'ヒ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'フ', 'フ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヘ', 'ヘ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ホ', 'ホ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'マ', 'マ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ミ', 'ミ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ム', 'ム')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'メ', 'メ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'モ', 'モ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヤ', 'ヤ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ユ', 'ユ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ヨ', 'ヨ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ラ', 'ラ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'リ', 'リ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ル', 'ル')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'レ', 'レ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ロ', 'ロ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ワ', 'ワ')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, 'ン', 'ン')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '゚', '゜')
	SET @s = REPLACE(@s COLLATE Japanese_BIN, '゙', '゛')
	RETURN @s
END

やさしいT-SQL入門 (DB SELECTION)