文字コードの変換

[Tackey]

漢字の16進数コードの変換に困っています。

あるテーブル（A）には、漢字項目列（例えば、氏名。varchar）があります。
別のテーブル（B）には、漢字のコードが文字列として登録されています。（char(4))
漢字コードとはUnicodeであり、ご存知のように例えば「あ」であれば3042の16進数です。

やりたいことは、以下のようなことです。
Bテーブルのある列が１である漢字コードを氏名に含むAテーブルを抽出する。

漢字を16進数に変換するには、２Byte目、1Byte目をそれぞれcastでbinaryに変換して結合しますと上記「あ」であれば、0x3042となります。
しかし、3042といった文字に変換できません。
逆に文字コードをバイナリにできません。（A～Fの変換でエラーになります）

VB.NET使用すればCovert関数でできそうですが、SQLだけで実現するにはどうすれば、よいのでしょうか？

[tamatama]

Post by Tackey
漢字の16進数コードの変換に困っています。
あるテーブル（A）には、漢字項目列（例えば、氏名。varchar）があります。
別のテーブル（B）には、漢字のコードが文字列として登録されています。（char(4))
漢字コードとはUnicodeであり、ご存知のように例えば「あ」であれば3042の16進数です。
やりたいことは、以下のようなことです。
Bテーブルのある列が１である漢字コードを氏名に含むAテーブルを抽出する。
漢字を16進数に変換するには、２Byte目、1Byte目をそれぞれcastでbinaryに変換して結合しますと上記「あ」であれば、0x3042となります。
しかし、3042といった文字に変換できません。
逆に文字コードをバイナリにできません。（A～Fの変換でエラーになります）
VB.NET使用すればCovert関数でできそうですが、SQLだけで実現するにはどうすれば、よいのでしょうか？

両方のテーブルの扱う文字列がUnicodeであれば、問題なくSQL文だけで記述できるのでは？

[Tackey]

Post by tamatama
両方のテーブルの扱う文字列がUnicodeであれば、問題なくSQL文だけで記述できるのでは？

１．漢字項目（varchar）から取得される漢字コードの結果は、0x付きのバイナリ
　　例）「あ」であれば、0x3042
２．漢字コードの列は0xなしの文字列
　　例）「あ」であれば、3042

双方の比較は型が違うので、出来ません。
１の漢字項目を２の「0x」なしの文字列、または２の文字列を0x付きのものにしたいのです。
（VB.NETであれば、Convert.ToInt32とToCharでできるようですが）

[tamatama]

Post by Tackey

Post by tamatama
両方のテーブルの扱う文字列がUnicodeであれば、問題なくSQL文だけで記述できるのでは？

１．漢字項目（varchar）から取得される漢字コードの結果は、0x付きのバイナリ
　　例）「あ」であれば、0x3042
２．漢字コードの列は0xなしの文字列
　　例）「あ」であれば、3042
双方の比較は型が違うので、出来ません。
１の漢字項目を２の「0x」なしの文字列、または２の文字列を0x付きのものにしたいのです。
（VB.NETであれば、Convert.ToInt32とToCharでできるようですが）

（案１）
SUBSTR(expression, start, length)とRIGHT(expression, length)という組み込み文字列操作関数があります。
また、+(文字列連結)もあります。それらを使って、文字列整形すればよいのでは？
（案2）
B_tableの漢字コードをChar(4)の代わりにWChar(1)とすればよいのでは？
UNICODEという組み込み関数があります。

[Tackey]

Post by tamatama
（案１）
SUBSTR(expression, start, length)とRIGHT(expression, length)という組み込み文字列操作関数があります。
また、+(文字列連結)もあります。それらを使って、文字列整形すればよいのでは？

使用している関数は以下のとおりです
（漢字項目はKANJIという列で、1文字目のコードを取得しています。）
SUBSTRING(SUBSTRING(CAST(KANJI as binary), 2, 1)+
SUBSTRING(CAST(KANJI as binary), 1, 1)
列の内容が「あ」の場合、結果は「0x3042」です。
この結果はあくまでもBinaryで、文字にはなりません。
（リトルエンディアンで考えています）

Post by tamatama
（案2）
B_tableの漢字コードをChar(4)の代わりにWChar(1)とすればよいのでは？
UNICODEという組み込み関数があります。

例えば3042という文字があって、UNICODEをやると1文字目（３）の「51」が結果となります。

[tamatama]

Post by Tackey

Post by tamatama
（案１）
SUBSTR(expression, start, length)とRIGHT(expression, length)という組み込み文字列操作関数があります。
また、+(文字列連結)もあります。それらを使って、文字列整形すればよいのでは？

使用している関数は以下のとおりです
（漢字項目はKANJIという列で、1文字目のコードを取得しています。）
SUBSTRING(SUBSTRING(CAST(KANJI as binary), 2, 1)+
SUBSTRING(CAST(KANJI as binary), 1, 1)
列の内容が「あ」の場合、結果は「0x3042」です。
この結果はあくまでもBinaryで、文字にはなりません。
（リトルエンディアンで考えています）

先頭の文字のUNICODEを知るには、（案２）の組み込み関数を使えばよいのでは。
具体的なサンプルが見つかったのでそれをポイントしておきます。
MSDNの日本語を見つけるのは至難の技なので。また、MSDNの内容をコピペすると著作権侵害になりそうなので
とりあえず、英文。内容は、UNICODEの文字列の任意の位置の1文字についてUNICODEを返すというもの。

サンプルは以下のURL
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_7g11.asp

Post by Tackey

Post by tamatama
（案2）
B_tableの漢字コードをChar(4)の代わりにWChar(1)とすればよいのでは？
UNICODEという組み込み関数があります。

例えば3042という文字があって、UNICODEをやると1文字目（３）の「51」が結果となります。

説明不足でした。Char(4)で4桁の文字列で表現するのなら、1文字のワイド文字にして、対象の1文字を入力しておけば、漢字コードを知りたいときにはUNICODEで知ることができますよねという意味合いです。
例
　　　　　　　　@Code = select B_Table.漢字コード from B_Table where
B_Table.ある項目 = 1
　　　　　　　　Select * from A_Table where A_Table.漢字項目 Like '%' +
@Code + '%'

長いことSQLServerを触っていないので、文法には自身がないがこんなもんだろう程度。

そのコードを知りたい場合には
Select UNICODE( B_Table.漢字コード) from B_Table

[Tackey]

Post by tamatama
例
B_Table.ある項目 = 1
　　　　　　　　Select * from A_Table where A_Table.漢字項目 Like '%' +
@Code + '%'
長いことSQLServerを触っていないので、文法には自身がないがこんなもんだろう程度。
そのコードを知りたい場合には
Select UNICODE( B_Table.漢字コード) from B_Table

やりたいことは、そんな感じなんです。
B_Table.漢字コードに「あ」のコード「3042」が格納されており、「あ」を結果として取得したいというものです。
しかし、UNICODE(SUBSTRING(漢字コード,1,1))+UNICODE(SUBSTRING(漢字コード,2,1))....とやっても３、０、４、２をそれぞれUNICODE変換しただけのものを加算しただけです。

[tamatama]

Post by Tackey

Post by tamatama
例
B_Table.ある項目 = 1
　　　　　　　　Select * from A_Table where A_Table.漢字項目 Like '%' +
@Code + '%'
長いことSQLServerを触っていないので、文法には自身がないがこんなもんだろう程度。
そのコードを知りたい場合には
Select UNICODE( B_Table.漢字コード) from B_Table

やりたいことは、そんな感じなんです。
B_Table.漢字コードに「あ」のコード「3042」が格納されており、「あ」を結果として取得したいというものです。
しかし、UNICODE(SUBSTRING(漢字コード,1,1))+UNICODE(SUBSTRING(漢字コード,2,1))....とやっても３、０、４、２をそれぞれUNICODE変換しただけのものを加算しただけです。

大体わかったので…

SQL文

select nchar(unicode('あ'))
select nchar(0x3042)
select ((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer)
select nchar(((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer))
go

実行結果
あ
あ
12354
あ


で、求めることができるのでは？

[tamatama]

Post by tamatama

Post by Tackey

Post by tamatama
例
B_Table.ある項目 = 1
　　　　　　　　Select * from A_Table where A_Table.漢字項目 Like '%' +
@Code + '%'
長いことSQLServerを触っていないので、文法には自身がないがこんなもんだろう程度。
そのコードを知りたい場合には
Select UNICODE( B_Table.漢字コード) from B_Table

やりたいことは、そんな感じなんです。
B_Table.漢字コードに「あ」のコード「3042」が格納されており、「あ」を結果として取得したいというものです。
しかし、UNICODE(SUBSTRING(漢字コード,1,1))+UNICODE(SUBSTRING(漢字コード,2,1))....とやっても３、０、４、２をそれぞれUNICODE変換しただけのものを加算しただけです。

大体わかったので…
SQL文
select nchar(unicode('あ'))
select nchar(0x3042)
select ((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer)
select nchar(((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16)
+ cast('4' as integer)) * 16 + cast('2' as integer))
go
実行結果
あ
あ
12354
あ
で、求めることができるのでは？

読みにくいので…

select nchar(unicode('あ'))

select nchar(0x3042)

select ((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer)

select nchar(((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16)
+ cast('4' as integer)) * 16 + cast('2' as integer))

go


実行結果

あ
あ
12354
あ

P.S.
最後のSQL文に対して、B_Tableの漢字コードをsubstringで4桁を1桁ずつ分割して16進数に変換するようにすれば、漢字コードの入ったchar(4)から、文字を取得できるのでは？

[Tackey]

Post by tamatama
select nchar(unicode('あ'))
select nchar(0x3042)
select ((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer)
select nchar(((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer))
go
実行結果
あ
あ
12354
あ
で、求めることができるのでは？

確かに「あ」については出来ました。ありがとうございます。

しかし、HexコードでA～Fが含まれている文字では
varchar 値 から int データ型に変換できませんでした。（メッセージ 245）
が出て、エラーとなってしまいます。

[tamatama]

Post by Tackey

Post by tamatama
select nchar(unicode('あ'))
select nchar(0x3042)
select ((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer)
select nchar(((((cast('3' as integer) * 16 ) + cast('0' as integer)) * 16) +
cast('4' as integer)) * 16 + cast('2' as integer))
go
実行結果
あ
あ
12354
あ
で、求めることができるのでは？

確かに「あ」については出来ました。ありがとうございます。
しかし、HexコードでA～Fが含まれている文字では
varchar 値 から int データ型に変換できませんでした。（メッセージ 245）
が出て、エラーとなってしまいます。

あっ、しっかり、忘れてました。リプレイスすればよいのでは…

（案１）
SQLServerってSQL文の中に条件文が入るような文法を持っていたと思うので
'A'以上のときは、binaryに変換して0x40を引くようにすればよいと思います。

（案２）
SQLのパズル的な方法として、16進変換テーブルを作って変換する方法もあります。

[tamatama]

Post by tamatama

Post by Tackey
確かに「あ」については出来ました。ありがとうございます。
しかし、HexコードでA～Fが含まれている文字では
varchar 値 から int データ型に変換できませんでした。（メッセージ 245）
が出て、エラーとなってしまいます。

あっ、しっかり、忘れてました。リプレイスすればよいのでは…
（案１）
SQLServerってSQL文の中に条件文が入るような文法を持っていたと思うので
'A'以上のときは、binaryに変換して0x40を引くようにすればよいと思います。
（案２）
SQLのパズル的な方法として、16進変換テーブルを作って変換する方法もあります。

アイデアだけ、

'A'（0x40) --> ':'(0x3A)
'B' (0x41)--> ';'(0x3B)
'C' (0x42)--> '<'(0x3C)
'D' (0x43)--> '='(0x3D)
'E' (0x44)--> '>'(0x3E)
'F' (0x45)--> '?'(0x3F)

に置き換える。Replace関数で…Replaceが6回ね…
その後、substringで1桁ずつ切り分けて、変換する。
そのとき、先ほどのメールではcastを使っていたが、ASCIIで行う。
cast('X' as integer）の代わりに(ASCII('X') - 0x30)へ置き換える。これによって16進に変換されます。

P.S.
でも、ここまで記述するのなら、ストアードプロシージャを1本書いたほうがよっぽど簡単で短くなります。
16進変換までは、ストアードプロシージャで記述したほうが良いのでは？

[tamatama]

Post by tamatama
アイデアだけ、
'A'（0x40) --> ':'(0x3A)
'B' (0x41)--> ';'(0x3B)
'C' (0x42)--> '<'(0x3C)
'D' (0x43)--> '='(0x3D)
'E' (0x44)--> '>'(0x3E)
'F' (0x45)--> '?'(0x3F)
に置き換える。Replace関数で…Replaceが6回ね…
その後、substringで1桁ずつ切り分けて、変換する。
そのとき、先ほどのメールではcastを使っていたが、ASCIIで行う。
cast('X' as integer）の代わりに(ASCII('X') - 0x30)へ置き換える。これによって16進に変換されます。
P.S.
でも、ここまで記述するのなら、ストアードプロシージャを1本書いたほうがよっぽど簡単で短くなります。
16進変換までは、ストアードプロシージャで記述したほうが良いのでは？

SQL文

select nchar((((ASCII(replace(replace(replace(replace(replace(replace('3',
'A', ':'), 'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30) *
16
+ (ASCII(replace(replace(replace(replace(replace(replace('0', 'A', ':'),
'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30) ) * 16
+ (ASCII(replace(replace(replace(replace(replace(replace('4', 'A', ':'),
'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30) ) * 16
+ (ASCII(replace(replace(replace(replace(replace(replace('2', 'A', ':'),
'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30))
go

実行結果
あ


こんな感じ

[Tackey]

Post by tamatama
select nchar((((ASCII(replace(replace(replace(replace(replace(replace('3',
'A', ':'), 'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30) *
16
+ (ASCII(replace(replace(replace(replace(replace(replace('0', 'A', ':'),
'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30) ) * 16
+ (ASCII(replace(replace(replace(replace(replace(replace('4', 'A', ':'),
'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30) ) * 16
+ (ASCII(replace(replace(replace(replace(replace(replace('2', 'A', ':'),
'B', ';'), 'C', '<'), 'D', '='), 'E', '>'), 'F', '?') ) -0x30))
go

To:tamatama様
何度も付き合っていただきありがとうございました。

上記サンプルを拝借させていただきます。

Oracleですともっと簡単に出来たと思うのですが、SQLSERVERですと大変なんですね(^^;