SQLを使い始めてまだ四ヶ月くらいの新米エンジニアです。
最近Treasure Dataを使用することが多いのですが、こうクエリを記載すれば良いのか！と思ったことを備忘録として残します。

「/」で区切られた値が数パターンある場合

例えば以下の表のように、１つのカラムに「/」が1個、2個、または3個というデータがあり、

test
FURNITURE/KITCHEN
FURNITURE/BEDROOM/TABLE

この中から「/」の一個目の値、2個目の値をそれぞれ別カラムで抽出したい場合は以下のように記載できます。

CASE 
  WHEN (LENGTH(test) - LENGTH(REPLACE(test, '/', ''))) / LENGTH('/') = 1 THEN SPLIT(test, '/')[1]
  WHEN (LENGTH(test) - LENGTH(REPLACE(test, '/', ''))) / LENGTH('/') = 2 THEN SPLIT(test, '/')[2]
END AS split1

CASE 
  WHEN (LENGTH(test) - LENGTH(REPLACE(test, '/', ''))) / LENGTH('/') = 1 THEN SPLIT(test, '/')[1]
  WHEN (LENGTH(test) - LENGTH(REPLACE(test, '/', ''))) / LENGTH('/') = 2 THEN SPLIT(test, '/')[2]
END AS split2

抽出結果は下記になります。

test	split1	split2
FURNITURE/KITCHEN	FURNITURE	KITCHEN
FURNITURE/KITCHEN/CHAIR	FURNITURE	BEDROOM