Last active
August 29, 2015 14:10
-
-
Save xxxkurosukexxx/67952072bc875cb89395 to your computer and use it in GitHub Desktop.
とあるカラムの値の一部分をさらに分解して取り出したい ref: http://qiita.com/xxxkurosukexxx/items/a5bdb37a2446d9f07bf1
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment