SQLite 文字列関数

Atlassian Analytics は SQLite を使用して、クエリ以外の Visual SQL ステップを強化しています。文字列や数値に使用できるコア関数はたくさんありますが、ここでは、文字列に使用できる一般的な関数をいくつか紹介します。

LENGTH(X)

LENGTH("column name")

文字列の文字数を返します。

LOWER(X)

LOWER("column name")

文字列をすべて小文字に変換します。

UPPER(X)

UPPER("column name")

文字列をすべて大文字に変換します。

REPLACE(X, Y, Z)

REPLACE("column name", 'replace_this', 'with_this')

文字列の文字または部分文字列を別の文字または文字列に置き換えます。

• X は、置換する文字または文字列を含む文字列を表します。

• Y は、置換する文字または部分文字列を表します。

• Z は、Y の置換に使用される文字または文字列を表します。

INSTR(X, Y)

文字列内の文字の位置を検索します。

• X は、位置を取得する文字を含む文字列を表します。

• Y は、位置を取得する文字列を表します。

その文字が文字列に複数回出現する場合、この関数は最初に出現する位置を返します。

SUBSTR(X, Y, Z)

SUBSTR("column name",'start_position','end_position')

作業している文字列の部分文字列を取得します。

• X は、部分文字列を取得する文字列を表します。

• Y は、部分文字列を取得する開始位置を表します (文字列の最初の文字位置は常に 1 です)。

• Z (オプション、省略可) は、結果文字列の文字数を表します。

SUBSTR() を使用して電話番号をフォーマットする

電話番号列の形式が 5556667777 になっているのに、(555) 666-7777 の形式にする必要がある場合は、SUBSTR() 関数を使用してフォーマットできます。

カスタム数式では、次の substr() 関数を使用します。

Phone を電話番号が含まれる列の名前に置き換えます。

SUBSTR() を使用してメールからドメイン名を取得する

さらに複雑な例を見てみましょう。各ユーザーの会社メールからドメイン名を取得するとします。メールは myname@mycompany.com のような一般的な形式に沿っていることがわかっています。

最終的な SUBSTR() 関数をもう少し理解しやすくするために、INSTR() 関数を使用して 2 つの情報を取得しましょう。

• 各メールでの @ の位置 (この列を「Position of @」と呼びます。)

  1 INSTR("Email", '@')

• 各メールでの .com の位置 (この列を「Position of .com」と呼びます。)

  1 INSTR("Email", '@')

それでは、メールからドメインを取得する方法を確認しましょう。SUBSTR() 関数には、X、Y、Z という 3 つのパラメーターがあることを思い出してください。

• X の値はメール アドレスを含む列で、簡単なものは “Email” です。

• Y 値の位置は、@ の後ろである必要があります。
  "Position of @"+1

• Z 値はドメインの長さである必要があります。これは、.com の前の位置から Y 値を引くことで得られます (つまり、(Z-1)-Y)。
  ("Position of .com"-1)-"Position of @"

最終的な SUBSTR() 式は次のとおりです。

少し手間がかかりましたが、各ユーザーのメールからドメイン名を正常に取得できました。