SQLServerでの文字列の半角文字から全角文字への変換
データの全角半角変換、、 - 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