About Me

My photo
Software Engineer at Starburst. Maintainer at Trino. Previously at LINE, Teradata, HPE.

2017-11-27

Teradata IN & regexp_split_to_table

TeradataのSQLでin句の中をパラメータとして文字列として受け取る小技を紹介したいと思います。

まずはデータを準備します。

drop table t1
;
create table t1(c1 int, c2 varchar(1))
;
insert into t1 values (1, 'a')
;
insert into t1 values (2, 'b')
;
insert into t1 values (3, 'c')
;
select * from t1
;
c1, c2
1,  a
2,  b
3,  c

上記のテーブルで1行目と3行目のレコードをin句で取得したい場合、以下のようなクエリになるかと思います。

select
*
from t1
where c2 in ('a', 'c')
order by 1
;
c1,c2
1, a
3, c

このカッコ内の文字列をパラメータとして受け取る際に、IFの都合でシングルクォートが自動でエスケープされてしまうなんてことがあるかもしれません。以下のようなクエリが渡された場合、Teradataはシンタックスエラーになります。

select
*
from t1
where c2 in (''a'', ''c'')
;

Teradataに詳しい方はoreplaceでシングルクォート2つを1つにすれば…と考えるかもしれませんが、この方法だとひとつの文字列として見なされてしまうので、レコードがヒットしなくなります。

select
*
from t1
where c2 in (oreplace('''a'', ''c''', '''''', ''''))
;

そこで登場するのがregexp_split_to_tableというマイナーな関数です。split_to_tableとあるように文字列をテーブルのように縦に分割してくれます。

select
*
from table (regexp_split_to_table(1,'''a'', ''c''', ',', 'i')
 returns (id integer, tokennum integer, token varchar(10) character set unicode)) as dt
;
id, tokennum, token
1, 1, ‘a’
1, 2, ‘c’

必要なのはtokenカラムなのでこれをtrimで整形してin句の条件にしてあげればできあがりです。

select
*
from t1
where c2 in (
 select
 trim(both '''' from trim(both ' ' from token)) as token
 from table (regexp_split_to_table(1,'''a'', ''c''', ',', 'i')
  returns (id integer, tokennum integer, token varchar(10) character set unicode)) as dt
)
order by 1
;

c1,c2
1, a
3, c

regexp_split_to_tableは本来こういった使い方をするための関数ではありませんが、こんな関数もあったなと覚えておくといつか役に立つかもしれないですし、立たないかもしれません👻