読者です 読者をやめる 読者になる 読者になる

MySQLでカラムの値から一部を取り出す

mysql sql

こんなテーブルと値があるとする。

id(number) profile(varchar)
1 sex:male, name:yamada, age:30
2 sex:female, name:sato, age:20

ここから、nameの値だけを取り出すことを目的にする。

name
yamada
sato

SQL

select
    LEFT( 
        SUBSTRING( 
            profile, 
            LOCATE('name:', profile) + LENGTH( 'name:' )
        ), 
        LOCATE ( 
            ',' , 
            SUBSTRING( 
                profile, 
                LOCATE('name:', profile) + LENGTH( 'name:' )
            )
        )
    ) AS "name"
from 
   hoge
;

処理の流れ

  1. name: の始まりの位置を取得する
  2. LOCATE('name:', profile)
  3. name値の始まりの位置を取得する
  4. name: の開始位置から、「name:」分ずれた位置と考える
  5. LOCATE('name:', profile) + LENGTH( 'name:' )
  6. name値の開始から文字列の終了までを切り出す
  7. SUBSTRING( profile, LOCATE('name:', profile) + LENGTH( 'name:' ))
  8. 「yamada, age:30」が取れている状態
  9. name値の終了位置を取得する
  10. 「,」が来たら終了と考える
  11. LOCATE ( ',' , 'yamada, age:30')
  12. なので、 LOCATE ( ',' , SUBSTRING( profile, LOCATE('name:', profile) + LENGTH( 'name:' )))
  13. name値の終了までを取り出す
  14. name値の開始から文字列の終了から、name値の終わりまでを切り取る
  15. LEFT( SUBSTRING( name, LOCATE('name:', profile) + LENGTH( 'name:' )), LOCATE ( ',' , SUBSTRING( profile, LOCATE('name:', profile) + LENGTH( 'name:' ))))

余談

本来であればカラムの値はそれだけで意味を為すようにしたい。 値を加工するにしてもできればSQLではやりたくない。 (SQLは問い合わせに振りきりたくて、ロジカルなことはあんまりやらせたくない派なので)

まぁでも、調査だったり既存のデータ構造だったりで力技が必要になることはある。