PostgreSQL 9.5でtextカラムの文字列からByte単位で文字列抽出

Question

PostgreSQL 9.5でtextカラムの文字列からByte単位で文字列抽出したいです。

Multi-ByteとSingle-Byteの文字が混在しているtextカラムから
先頭20byteを抜き出して抽出したいのですが、
Oracleの[to_single_byte]関数に似たものがないので対処方法がわかりません。

▼OracleのSQL
select TRIM(SUBSTRB('配管_SS塗装_ﾎﾟﾝﾌﾟ棟_低率_八仙',1,20)) 機器名称２０ from dual

→結果「配管_SS塗装_ﾎﾟﾝﾌﾟ棟_」

★★★追加質問★★★

PostgreSQLで以下のSQLだとエラーが出ます。

select convert_from(convert(substring(convert_to('縞鋼板_管理棟_ﾌﾞﾛﾜｰ室_北部浄', 'SJIS') from 1 for 20), 'sjis', 'utf8')::bytea, 'utf8')

----エラー内容----
ERROR: 符号化方式"SJIS"で無効なバイトシーケンスです: 0x8e (11793)
********** エラー **********
ERROR: 符号化方式"SJIS"で無効なバイトシーケンスです: 0x8e (11793)
SQLステート:22021

両方の文字列が正常に変換できるようにしたいのです。。。

Answer 1

select convert_from(convert(substring(convert_to('配管_SS塗装_ﾎﾟﾝﾌﾟ棟_低率_八仙', 'SJIS') from 1 for 20), 'sjis', 'utf8')::bytea, 'utf8')

勉強になりました。 m(__)m

Comments

[退会済みユーザー]

なるほどなるほど。
もしもWindowsで作ってきたデータであれば、SJIS-winを使うとさらによさげ。

[oQMan]

ありがとうございます！！！ありがとうございます！！！大変助かりました！！！

[oQMan]

すいません。追加質問を記載しました。宜しくお願いします。

Answer 2

全角/半角混在で20バイトという区切りであれば、当然20バイトの境界が全角文字に掛かる場合も出てきます。

縞鋼板_管理棟_ﾌﾞﾛﾜｰ室_北部浄だと室が分解されてそれをutf8に戻そうとすると変換できずにエラーになります。

今の仕様のまま行うならエラーを検知する必要がありますので、専用のストアド関数を作成するしかないと思いますけど。

若しくは、仕様を変更して全角文字に置き換えたものに対しての取り出しにする。
但し、全角/半角変換について最低限変換対象のリストはについては自作する必要がありますが。
※変換自体はtranslate

ただ、3番目の_以前を取り出すという事で良いなら、_区切りの配列に見立てて、以下の様に取り出せます。

select array_to_string((string_to_array(val,'_'))[1:3], '_')
from (values('配管_SS塗装_ﾎﾟﾝﾌﾟ棟_低率_八仙'),('縞鋼板_管理棟_ﾌﾞﾛﾜｰ室_北部浄')) as w(val)

Comments

[oQMan]

勉強になりましたありがとうございます

Answer 3

substring(string [from int] [for int])
とか使えませんかね？
9.5. バイナリ文字列関数と演算子
を参照するに、stringの箇所にbytea型も使えるようですし。
text型からbytea型にはふつうに型変換すればいいような気がしますし。

Comments

[oQMan]

Postgreで以下のSQLを実行しました。

①select substring('配管_SS塗装_ﾎﾟﾝﾌﾟ棟_低率_八仙'::bytea ,1, 20)
→結果「"\351\205\215\347\256\241_SS\345\241\227\350\243\205_\357\276\216\357"」

変換が必要と思いConvertしました

②select convert_from(substring('配管_SS塗装_ﾎﾟﾝﾌﾟ棟_低率_八仙'::bytea ,1, 20), 'UTF8')
→エラーが発生
ERROR: 符号化方式"UTF8"で無効なバイトシーケンスです: 0xef (11793)
********** エラー **********

ERROR: 符号化方式"UTF8"で無効なバイトシーケンスです: 0xef (11793)
SQLステート:22021

[退会済みユーザー]

BYTEA型として切り出しているために、ふつうには文字列ではなくなってしまいますから当然です。
それにUTF-8エンコーディングだと1バイト～3バイトになり質問者さんが想定する形にはならないはず。
むしろ、SJISエンコーディングに変換した後バイト単位で切り出して、末尾のバイトを後加工する、
として扱ったほうが意図に沿う形になりそうです。
が、具体的な書き方まではノウハウ不足でわかりかねます。すみません。

[oQMan]

SJISエンコーディングに変換とはいかでしょうか？

select convert_from('配管_SS塗装_ﾎﾟﾝﾌﾟ棟_低率_八仙'::bytea, 'SJIS')

→エラーが発生
ERROR: 符号化方式"SJIS"で無効なバイトシーケンスです: 0x99 (11793)
********** エラー **********

ERROR: 符号化方式"SJIS"で無効なバイトシーケンスです: 0x99 (11793)
SQLステート:22021

となりました。

[退会済みユーザー]

select convert(substring(convert('配管_SS塗装_ﾎﾟﾝﾌﾟ棟_低率_八仙', 'UTF-8', 'SJIS') from 1 for 20), 'SJIS', 'UTF-8')
なんて書いてみて期待したとおりにならないので、なにか見逃しているポイントが有るはず、ということで詰んでしまいました。