SQLでの文字列処理

sql
1create table test (
2  id integer not null auto_increment,
3  col1 varchar(255),
4  col2 varchar(255),
5  primary key(id)
6);
7insert into test (col1, col2) values ('xaaa', 'aaa');
8insert into test (col1, col2) values ('bbbx', 'bbb');
9insert into test (col1, col2) values ('cccxddd', 'ccc');
10insert into test (col1, col2) values ('xeeex', 'eee');
11insert into test (col1, col2) values ('xfffxggg', 'fff');

SQLでの文字列処理についてお聞きしたいです。col1をSQLだけで処理してcol2と同じにすることはできるでしょうか。

xaaa => aaa(先頭にxがあったら除く)
bbbx => bbb(終端にxがあったら除く)
cccxddd => ccc(途中にxがあったらx以降を除く)
xeeex => eee(先頭にも終端にもxがあったらそれぞれ除く)
xfffxggg => fff(先頭にも途中にもxがあったら先頭と途中のx以降を除く)

退会済みユーザー

2018/08/08 11:25 編集

col1に相当する情報はどこから来るのですか？あれ、違うか、既にテーブル上にそういうデータが有る場合のUPDATE文を質問していますか？

namda

2018/08/08 11:31

これDBは何使ってるんですか？mysql?postgre?sqlserver?plsql?

退会済みユーザー

2018/08/08 11:31

すでにテーブル上にそういうデータが入っている状況を考えています。不要な文字列を含んでDBに入ってきてしまう状況は避けられず、文字列を処理してUPDATEするか、別のテーブルに退避させるために処理した上でselectするなどしたいと考えています。

退会済みユーザー

2018/08/08 11:33

mysqlです

退会済みユーザー

2018/08/08 11:41

削除したいxは常に1文字なのか、それともいろんなパターンが有るのか。想定されるパターンを全部書き出してほしい。

退会済みユーザー

2018/08/08 11:42

パターンはこれですべてです。不要な1文字が書き出したパターンで入ってくることがあります。

sazi

2018/08/08 12:52

条件ではcol1にcol2の内容を上書きでコピーすればいいように見えるんだけど、そんな単純な話では無い？

退会済みユーザー

2018/08/08 12:56

col2は説明のためにつけたのですが、実際にはありません。テーブルの中にはcol1だけがある状態です。col1には不要な文字が入り込むことがあり、それをSQLだけで(DBの機能だけで)処理できないかと考えているところです。

namda

2018/08/08 13:28 編集

①途中からxが出現したらxから後方を全部消す。②先頭のxを消す。ってルールでなんとかなりそうな気がしますね。

行動規範の内容に同意します

回答2件

ベストアンサー

①途中からxが出現したらxから後方を全部消す。②先頭のxを消す。ってルールでなんとかなりそうな気がしますね。

と書きましたが
なんとかなりました

sql
1select d.*, REVERSE(INSERT(REVERSE(INSERT(d.col1,LOCATE('x',d.col1, 2), CHAR_LENGTH(d.col1), '')),LOCATE('x',REVERSE(INSERT(d.col1,LOCATE('x',d.col1, 2), CHAR_LENGTH(d.col1), '')), CHAR_LENGTH(INSERT(d.col1,LOCATE('x',d.col1, 2), CHAR_LENGTH(d.col1), '')) - 1), CHAR_LENGTH(INSERT(d.col1,LOCATE('x',d.col1, 2), CHAR_LENGTH(d.col1), '')), '')) from test d

回りくどいことしてるのでもうちょっとシェイプアップできるかもしれませんが
解説すると
１段目は

sql
1select d.*, REVERSE(INSERT(d.col1,LOCATE('x',d.col1, 2), CHAR_LENGTH(d.col1), '')) from test d

こうです。
これで2文字目以降にxが出現したら空文字で置換し、文字列を反転するという内容です。

文字を反転するのはLOCATE（n文字を探してhit文字以降を置換）はあっても「後ろか探し置換or削除する関数」が無いため反転します。

2段目は1段目の d.col1 の部分に1段目（ REVERSE(INSERT(d.col1,LOCATE('x',d.col1, 2), CHAR_LENGTH(d.col1), '')) ）をぶち込み、2文字目以降としてたところを置換後文字列長の-1以降に出現した場合空白置換＝先頭にxがアレば消すとしてます。
で
また反転し直して完了です。

投稿2018/08/08 14:33

namda

総合スコア705