IT業界の技術をメモ・情報公開できる

SQL カンマ区切りをフィールごとに

登録日:2023-08-02   
mysql @ms
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(カラム, ",", 1), ",", -1) AS カラム,
SUBSTRING_INDEX(SUBSTRING_INDEX(カラム, ",", 2), ",", -1) AS カラム,
SUBSTRING_INDEX(SUBSTRING_INDEX(カラム, ",", 3), ",", -1) AS カラム
FROM
テーブル

https://qiita.com/qwe001/items/3f54a5d12702a8eb67d7


【更新】
update テーブル,(SELECT RIID,SUBSTRING_INDEX(SUBSTRING_INDEX(カラム, ",", 1), ",", -1) AS カラム1 FROM テーブル) as b
set テーブル.カラム1=b.カラム1
where テーブル.RIID = b.RIID

update テーブル,(SELECT RIID,SUBSTRING_INDEX(SUBSTRING_INDEX(カラム, ",", 2), ",", -1) AS カラム2 FROM テーブル) as b
set テーブル.カラム2=b.カラム2
where テーブル.RIID = b.RIID

update テーブル,(SELECT RIID,SUBSTRING_INDEX(SUBSTRING_INDEX(カラム, ",", 3), ",", -1) AS カラム3 FROM テーブル) as b
set テーブル.カラム3=b.カラム3
where テーブル.RIID = b.RIID

一覧に戻る