やりたいこと
とあるカラムに入っている商品詳細ページの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 修正しました。)
- p1: C101
まとめ
もっといい方法があるはず...。