LoginSignup
3

More than 5 years have passed since last update.

とあるカラムの値の一部分をさらに分解して取り出したい

Last updated at Posted at 2014-12-02

やりたいこと

とあるカラムに入っている商品詳細ページのURLから商品コードを_区切りで分解して取り出したい。
http://www.example.com/item/detail?shop=XXX&product=AAAA_BBBB_CCCC_DDDD&ref=top
↑これを、p1=AAAA, p2=BBBB, p3=CCCC, p4=DDDDにしたい、ってこと。
(※なんでURLが入ってて商品コードが入ってないのかというツッコミは今回はスルーでお願いします。)
(※実際、今回本当は他のテーブルに分解されてる値が入ってるからJOINすればよかったけど...まぁいいか。)
(※今回は極端な例だけど、電話番号を-で分解したいとか?需要があるかはしらないけど。)
(※ていうかSQLで頑張る必要あったのだろうか。プログラム側で正規表現とかでどうにかなったのでは。)

動作はMySQL5.5で確認。

カラムの中身

http://www.example.com/item/detail?shop=XXX&product=AAAA_BBBB_CCCC_DDDD&ref=top
(※実際のURLは書けないから適当にそれっぽいの。)
- XXXは3桁の数字
- AAAA_BBBB_CCCC_DDDD_ 区切りの可変長文字列

SQL

SELECT
    -- ↓先頭から1つ目の_の前まで
    SUBSTRING_INDEX(product, '_', 1) as p1,
    -- ↓先頭から2つ目の_の前までから、1つ目の_の前まで+_を''(空文字)に置き換えたもの
    SUBSTRING(REPLACE(SUBSTRING_INDEX(product, '_', 2), SUBSTRING_INDEX(product, '_', 1), '') FROM 2) as p2,
    -- ↓先頭から3つ目の_の前までから、2つ目の_の前まで+_を''(空文字)に置き換えたもの
    SUBSTRING(REPLACE(SUBSTRING_INDEX(product, '_', 3), SUBSTRING_INDEX(product, '_', 2), '') FROM 2) as p3,
    -- ↓先頭から4つ目の_の前までから、3つ目の_の前まで+_を''(空文字)に置き換えたもの
    SUBSTRING(REPLACE(SUBSTRING_INDEX(product, '_', 4), SUBSTRING_INDEX(product, '_', 3), '') FROM 2) as p4
FROM (
    SELECT
        REPLACE(
            REPLACE(
                SUBSTRING(
                    REPLACE(item_detail_url, 'http://www.example.com/item/detail?shop=','') -- 頭のいらない部分切り落とし
                FROM 4) -- shop=XXXのXXXを切り落とし
            , '&product=','') -- 更に余計な部分を切り落とし(よく考えたら↑のFROMの数字をずらせばよかった)
        , '&ref=top', '') as product -- 後ろのゴミも切り落とし
    FROM
        items
) as i

結果

  • http://www.example.com/item/detail?shop=100&product=A0123_45_6789_A&ref=top
    • p1: A0123
    • p2: 45
    • p3: 6789
    • p4: A
  • http://www.example.com/item/detail?shop=100&product=C101_987_65BB_ZZA&ref=top
    • p1: C101
      • (16:58 修正しました。)
    • p2: 987
    • p3: 65BB
    • p4: ZZA
      • (16:58 修正しました。)

まとめ

もっといい方法があるはず...。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3