SQL 形式モデル

Snowflakeでは、 SQL 形式モデル(形式文字列を含むリテラル)を使用して、数値をテキスト文字列に、またはその逆に変換する方法を指定します。そのため、これらは TO_CHAR , TO_VARCHAR および TO_DECIMAL , TO_NUMBER , TO_NUMERIC 変換関数の引数として指定できます。

注釈

Snowflakeは、日付、時刻、タイムスタンプに対して、限定的な SQL 形式モデルのサポートも一部提供します( 日付と時刻の関数 および 変換関数 を参照)。SQL 形式モデルを使用して日付、時刻、タイムスタンプをフォーマットするフルサポートは、今後のリリースで追加される予定です。

このトピックの内容:

形式モデルのコンポーネント

形式モデルは、形式要素とリテラルの文字列で構成されます。

形式要素

形式要素は、数字および/または文字のシーケンス(ほとんど大文字と小文字を区別しない)、および記号の場合があります。形式要素は、互いに直接連結できます。

一部の形式要素は、入力テキストの出力と一致を制御するために、すべての形式モデルで共通して使用されます。他の形式要素には、キャストに使用される値の型に基づいた特定の用途があります。詳細については、このトピックの次のセクションをご参照ください。

形式リテラル

形式リテラルは、次の組み合わせで構成されるシーケンスです。

  • 二重引用符で区切られた任意の文字列(二重引用符は、隣接する2つの二重引用符として表される)。

  • 次の1つ以上の記号:

    記号/文字

    注意

    . (ピリオド)

    固定数値モデルでは、 09、または X に続く場合は形式要素として扱われます。それ以外の場合はそのまま保存されます。

    , (コンマ)

    数値モデルでは、 09、または X に続く場合は形式要素として扱われます。それ以外の場合はそのまま保存されます。

    ; (セミコロン)

    常にそのまま保存されます。

    : (コロン)

    常にそのまま保存されます。

    - (マイナス記号)

    常にそのまま保存されます。

    = (等号)

    常にそのまま保存されます。

    / (スラッシュ)

    常にそのまま保存されます。

    ( (左括弧)

    常にそのまま保存されます。

    ) (右括弧)

    常にそのまま保存されます。

リテラルは常にそのままの形で、形式モデル内の場所で正確に出力されます。

以下に、 SQL 形式モデルを使用して、番号の前ではなく番号の後にマイナス記号を出力する簡単な例を示します。 MI は、数値が負の数である場合にマイナス記号を配置する場所を示します。

select to_varchar(-123.45, '999.99MI') as EXAMPLE;

出力は、デフォルトの -123.45 ではなく 123.45- に似ています。

その他の例は、このトピックの最後に含まれています。

形式修飾子と汎用スペース処理

次の表は、入力テキストの出力と一致を制御し、すべての形式モデルに共通する特別な形式要素を示しています。

要素

説明

_ (アンダースコア)

何も出力されません。入力のオプションのスペースです。

FM

Fillモード修飾子。モデル内の修飾子に続く要素の compact モードと fill モードを切り替えます。

FX

完全一致修飾子。モデル内の修飾子に続く要素の 不完全完全 一致モードを切り替えます。

注釈

Fillモード修飾子は、テキストの最小数値形式要素(TMTM9TME)には影響しません。

Fillモード修飾子を使用した出力文字列の出力

デフォルトでは、Fillモードは fill に設定され、 FM Fillモード修飾子はそれを compact に切り替えます。繰り返し使用すると、 fill などに戻ります。

ほとんどの場合、出力で fill モードを使用すると、左側に数字の先頭にゼロまたはスペースを埋め込み、右側にテキストを埋め込むことで、形式要素が固定幅の出力を生成することが保証されます。これにより、固定幅フォントの列状出力が確実に整列されます。

compact モードでは、ほとんどの形式要素は最小幅の出力のみを生成します(先行ゼロとスペース、および後続スペースは抑制される)。

これらの規則に従わない形式要素は、以下に明示的に記載されています。

完全一致修飾子 FX は出力に影響しません。アンダースコア形式要素は何も出力しません。

修飾子を使用した入力文字列の解析

入力文字列の解析は、Fillモード修飾子 FM と完全一致修飾子 FX の両方の影響を受けます。初期:

  • Fillモードは fill に設定され、 FM はそれを compact に切り替えて戻します。

  • 完全一致モードは lax に設定され、 FX はそれを exact に切り替えて戻します。

解析中の形式要素およびリテラルに対するすべての文字列照合では、大文字と小文字が区別されません。

lax モードでは、入力解析の最初のステップは、先頭の空白(一連のスペース、タブ、 LF、 CR、 FF、 VT 文字)をスキップすることです。最初の形式要素が FX の場合、入力開始時のモードは厳密です。それ以外の場合は lax です。

注釈

通常のスペース文字のみが、 値内 で解析を許可されます(コンポーネントを別の行に配置したり、タブで区切ったりすることはできない)。

lax 一致モードでは、リテラル内のスペースは、空ではない任意のスペースの入力シーケンスと照合されます。スペース以外の文字は1対1で一致します。 exact モードでは、リテラル内のすべての文字は入力文字と1対1で一致する必要があります。

数値形式要素は、対応する数字列と照合されます。

  • fill モードと exact モードの両方が有効な場合、桁数は対応する数値形式要素の幅に正確に対応する必要があります(先行ゼロが予想される)。

  • compact または lax モードが有効な場合、一致する入力番号には、最大で形式要素の最大幅に等しい桁数、および少なくとも1桁が必要です。先行ゼロは無視されます。

テキスト形式の要素は、大文字と小文字を区別せずに一致します。

  • fill モードと exact モードの両方が有効な場合、要素の最大幅までの後続スペースの数が予想されます。

  • それ以外の場合、可変長テキスト要素の後のスペースは lax モードでは無視され、 exact モードでは実際の単語(パディングスペースなし)との完全一致が期待されます。

最後に、現在のモードが lax の場合、入力文字列の最後までの末尾の空白は無視されます。

通常、 lax モードと exact モードの両方では、形式モデルにスペースが存在しないか、 fill モードで形式要素のコンテンツを出力しても生成できないスペースの照合は許可されません。

注釈

この動作は、任意の2つの形式要素の間にスペースを挿入できるOracle laxの一致セマンティクスとは異なります。Snowflakeは、より厳密な一致セマンティクスを使用して、自動データ型認識中の過度の誤一致を回避します。

lax モードと exact モードの両方にスペースが存在する場合、スペースを無視する場所は、 _ (アンダースコア)形式要素を使用して明示的にマークできます。

経験則として、 exact モードの形式は、同じ形式で出力された入力文字列のみを認識しますが、 lax モードの形式は、Fillモード修飾子が追加または削除された同様の形式で印刷された入力文字列を認識します。

数値形式モデル

数値形式モデルは、2つの型をサポートしています。

  • 固定位置( 09、または X 形式の要素が配置される桁の明示的な配置を使用)

  • テキスト最小(TMTMETM9 形式の要素)

注釈

これらの2つの型を同じモデル内で混在させることはできません。

固定位置の数値形式

注釈

このセクションでは、非負の固定位置番号について説明します。固定位置数値形式の出力での数値の符号の配置の詳細については、 固定位置形式の符号位置 をご参照ください。

固定位置番号は、数字要素 0 または 9 を使用して表されます。例えば、 999 は、1から3の10進数を保持します。数値の小数部分は、区切り要素 . (ピリオド)または D を使用して区切られます。

  • . は、常にピリオドとしてレンダリングされます。

  • D 要素に別の文字を使用するには、入力文字列を変更して、キャスト関数を適用する前にすべてのピリオドをコンマに、すべてのコンマをピリオドに置き換えます。

通常、整数部の先行ゼロと小数部の後続ゼロはスペースで置き換えられます(整数部の値がゼロの場合を除き、その場合は単一の 0 文字としてレンダリングされる)。この動作を抑制するには、 9 の代わりに 0 形式要素を使用します。対応する位置には 0 文字が保持されます。形式要素 B を数値の前に使用すると、整数値の最後の 0 の保持が抑制されます( B を使用し、数値の整数部分の値がゼロの場合、すべての数字はスペースとしてレンダリングされる)。

数字のグループ区切り記号 , (コンマ)または G は、数字が十分に大きく、数字がグループ区切り記号の両側にある場合、対応するグループ区切り文字が出力されます。通貨合計の出力に役立つ形式の例は 999,999.00 です。

形式の桁位置よりも数字の整数部分の桁数が多い場合、オーバーフローを示すためにすべての桁が # として出力されます。

指数要素により、整数部の最初の桁が1から9になるように固定位置の数値が正規化されます(数値の値がゼロでない限り、指数の値もゼロになる)。 EE 要素は指数の正しい桁数を自動的に選択し、 + 記号を出力しませんが、 EEEEEEEEEEEE は常に指数および要求された桁数の + または - 記号を出力します。(先行ゼロは抑制されない)。指数オーバーフローは、数字の代わりに # で示されます。

指数インジケータは、形式要素の最初の文字の大文字小文字に応じて、大文字の E または小文字の e を出力します。

X 形式要素は 9 と同様に機能しますが、16進数字 0-9A-F が出力されます。現在、16進小数はサポートされていません。 9 と同様に、 X は先行ゼロをスペースに置き換えます。 0 要素は、 X と組み合わせて使用すると、先行ゼロ抑制なしで16進数を出力します(したがって、 000X を使用して、常に4桁を含む16進数を出力します)。

X は大文字のラテン文字を含む16進数を出力し、小文字の x は小文字のラテン文字を出力します。16進数の 0 形式要素は、後続の X 形式要素の大文字と小文字を使用します。

通常、16進数は符号なしで出力されます。つまり、負の数値には最上位ビットにすべて 1 が含まれますが、 X 要素を明示的な符号(S または MI)とともに使用すると、 - 記号が絶対値とともに出力されます。

固定位置の数値形式モデルは、浮動小数点数の特別な値(無限大または非数値)のオーバーフローを報告します。

固定位置形式要素

次の表に、固定位置形式でサポートされる要素を示します。次の点にご注意ください:

  • 反復可能 列は、形式モデルで要素を繰り返すことができるかどうかを示します。そうでない場合、要素は形式モデルごとに1回しか使用できません。

  • 大文字と小文字を区別 列は、要素の大文字と小文字が形式に影響する要素を示します。例:

    • EE は、大文字の E で指数を処理します。

    • ee は、小文字の e で指数を処理します。

    他のすべての要素は大文字と小文字を区別しません。

要素

反復可能

大文字と小文字を区別

説明

$

数字の桁の前(通常は記号の後)に出力されたドル記号。

. (ピリオド)

小数の区切り記号。常にピリオドとして出力されます。

, (コンマ)

数字グループ区切り記号。コンマまたは空白スペースとして出力されます。

0

桁の位置。先頭/末尾のゼロは明示的に出力されます。

9

桁の位置。先頭/末尾のゼロは空白に置き換えられます。

B

後続の数字のスペースとしてゼロ値を表すことを強制します。

D

小数の区切り記号。 . 要素の代替です(上記の説明を参照)。

EE

可変幅指数(2~5文字)。整数の + 記号なし(例: E0E21E200E-200)。

EEE

固定幅の指数(3文字)。範囲は E-9E+9 までです。

EEEE

固定幅の指数(4文字)。範囲は E-99E+99 までです。

EEEEE

固定幅の指数(5文字)。範囲は E-324E+308 までです。

G

数字グループ区切り記号。 , の代替です(上記の説明を参照)。

MI

明示的な数値記号プレースホルダー。正数の場合はスペースを、負数の場合は - 記号を出力します。

S

明示的な数値記号プレースホルダー。正数の場合は + 記号を、負数の場合は - 記号を出力します。

X

16進数。

固定位置形式の符号位置

デフォルトでは、固定位置形式は常に数値の符号用スペースを予約します。

  • 非負数の場合、デフォルトの空白スペースが最初の数字の前に出力されます。

  • 負の数値の場合、デフォルトの空白スペースと - 記号は、最初の数字(または小数、 B 形式要素が使用される場合は小数)の前に出力されます。

ただし、 SMI$ 形式の要素を使用して、番号の符号や空白スペースの場所を明示的に指定できます。

例(これらの例では、空白が挿入される場所を示すためにアンダースコア _ が使用されています):

形式モデル

12 出力:

-7 出力:

99

_12

_-7

S99

+12

_-7

99S

12+

_7-

MI99

_12

-_7

99MI

12_

_7-

$99

_$12

_-$7

固定位置形式とFillモード修飾子を使用した数字の出力

fill モードでは、 EEMI などの可変長形式要素の右側にスペースが埋め込まれます。

compact モードでは、可変長要素を含む数値形式要素から生じるすべてのスペースが削除されるため、結果の文字列は短くなり、整列しなくなります。例(空白スペースなし):

形式モデル

12 出力:

-7 出力:

FM99

12

-7

固定位置形式と修飾子を使用した数値の解析

数字を含む文字列の解析は、 FX および FM 修飾子の両方の影響を受けます。

  • lax モードの場合:

    • 数字のグループ区切り記号はオプションです(グループ区切り記号の有無にかかわらず、数字は一致するが、各グループ区切り記号間の数字の数字は一致する必要がある)。また、 MI 形式要素に有効な一致として + を許可します。

    • lax モードでは、 0 形式の要素に一致する数字(先頭または末尾のゼロも)が存在する必要があるという要件は無効になりません。

    • lax モードでは、先頭の記号と最初の数字の間にスペースを入れることができます。

    • また、 lax モードでは、すべての指数形式要素(EEEEEEEEEEEEEE)は EE として扱われ、1~3桁の指数指定とオプションの + または - 記号に一致します。。

    • B を使用して、整数部分に数字のない一致する番号を許可します。空の小数部の前の小数点は、 lax モードではオプションです。

  • exact モードの場合:

    • 番号は、形式に一致するように、省略された数字の代わりに適切な数のスペースを持っている必要があります(fill モードではスペース、 compact モードではスペースの不足)。

    • exact モードではグループ区切り文字を省略することはできません。さらに MI+ 記号と一致しません。

    • EE 以外の指数形式要素は、形式要素に必要な符号位置を正確な桁数と一致する必要があります。

    • 形式モデルで指定された場所の小数点は必須です。

テキスト最小の数値形式

固定位置の数値形式モデルでは常に桁数が明示的に指定されますが、テキスト最小形式要素では、数値の値に基づいて最小桁数が使用されます。 TM* 形式の要素はFillモード修飾子(fill または compact)に関係なく、常にスペースなしの可変長出力を生成します。

  • TM9 は、数値の値に基づいて、整数または小数として数値を出力します。10進数の固定小数点数値は、数値のスケールによって決定される小数部の桁数で正確に出力されます(末尾のゼロは fill モードで保持されます)。

  • 浮動小数点数の場合、 TM9 は、数値の指数に基づいて小数桁の数を選択します(2進数から10進数への正確な変換はできません)。浮動小数点数の大きさが大きすぎて位置表記が長すぎる場合、科学表記に切り替えます(以下の TME を参照)。浮動小数点数が小さすぎる場合、 TM9 はゼロを出力します。

  • TME は、科学表記法で数値を出力します。つまり、指数( EE と同じ)と小数部の整数位置に1桁を付けて出力します。指数インジケータのケース(E または e)は、形式要素の最初の文字(T または t)のケースと一致します。

  • TM は、数値の大きさに応じて TM9 または TME のいずれかを選択し、精度を維持しながらテキストの長さを最小化します。

テキスト最小形式要素

次の表に、テキスト最小形式でサポートされている要素を示します。次の点にご注意ください:

  • テキスト最小形式の文字列内で要素を繰り返すことはできません。

  • 大文字と小文字を区別 列は、要素の大文字と小文字が形式に影響する要素を示します。例:

    • TME は、大文字の E で指数を処理します。

    • tme は、小文字の e で指数を処理します。

    他のすべての要素は大文字と小文字を区別しません。

要素

反復可能

大文字と小文字を区別

説明

$

数字の桁の前(通常は記号の後)にドル記号が挿入されます。

TM

テキストの最小数、 TM9 または TME のいずれか短い方です。

TM9

位置表記のテキスト最小数です。

TME

科学表記法でのテキスト最小数です(指数付き)。

B

後続の数字のスペースとしてゼロ値を表すことを強制します。

MI

明示的な数値記号プレースホルダー。 - またはスペースになります。

S

明示的な数値記号プレースホルダー。 - または + になります。

テキスト最小形式の符号位置

デフォルトでは、テキスト最小形式の記号は次のいずれかです。

  • - 負の数の場合、数字の前に付けられます。

  • 非負数の場合は省略されます。

$SMI 要素は、固定位置形式モデルと同じ効果があります。浮動小数点数には、それぞれ正の極小値と負の極小値を表す2つの異なるゼロ値(+0. および -0.)があります。

テキスト最小形式と修飾子を使用した数値の解析

テキスト最小形式モデルを使用した解析は、 FX または FM 修飾子の影響を受けません。ただし、上記のように、明示的な符号要素 S および MI は影響を受けます。

TM9 位置表記法の任意の10進数(整数または小数)に一致します。科学表記法の数値と一致しません(指数付き)。反対に:

  • TME は、科学表記法のみに一致します。

  • TM は、両方に一致します。

テキストの最小要素に一致する番号には、スペースまたは数字グループ区切り文字を含めることはできません。

指数要素内の文字と16進数は、大文字と小文字を区別せずに常に一致します(小文字または大文字)。

代替、自動、デフォルトの形式

要素

説明

| (パイプ)

代替形式を区切ります。

AUTO

自動形式です。

文字列を解析するとき、形式文字列を | 文字で区切ることにより、複数の代替形式を指定できます。いずれかの形式に一致する場合、文字列は正常に解析されます。入力文字列が複数の形式に一致する場合、変換には任意の形式が使用されます。

解析に使用される形式全体をキーワード AUTO に置き換えることができます。これにより、ソース値または結果値の型に応じて、1つ以上の代替自動形式が挿入されます。カスタム形式を自動形式に追加するには、代替手段の1つとして AUTO を使用します。

デフォルトの形式は、キャスト関数で形式が明示的に指定されていない場合、入力値の解析( CSV ファイル)、および結果の出力に使用されます。

出力のデフォルト形式

次の表に、出力のデフォルト形式を示します。

SQL データ型

パラメーター

デフォルト形式

DECIMAL

なし

TM9

DOUBLE

なし

TME

解析のデフォルト形式

次の表に、解析のデフォルト形式を示します。

SQL データ型

パラメーター

デフォルトの AUTO 形式

DECIMAL

なし

TM9

DOUBLE

なし

TME

オプティミスティック文字列の自動変換(数値として自動的に認識される文字列)に使用される形式のリストは、上記のデフォルト入力形式の表にあるすべての形式の結合です。

出力例

次の例は、先頭にゼロを付けて数字を表示する方法を示しています。

create table sample_numbers (f float);
insert into sample_numbers (f) values (1.2);
insert into sample_numbers (f) values (123.456);
insert into sample_numbers (f) values (1234.56);
insert into sample_numbers (f) values (-123456.789);
select to_varchar(f, '999,999.999'), to_varchar(f, 'S000,000.000') from sample_numbers;

出力は次のようになります。

+------------------------------+-------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'S000,000.000') |
+==============================+===============================+
|        1.2                   | +000,001.200                  |
+------------------------------+-------------------------------+
|      123.456                 | +000,123.456                  |
+------------------------------+-------------------------------+
|    1,234.56                  | +001,234.560                  |
+------------------------------+-------------------------------+
| -123,456.789                 | -123,456.789                  |
+------------------------------+-------------------------------+

数字を揃えるために先行ゼロは必要ありません。デフォルトのFillモードは「fill」です。つまり、先頭の空白を使用して、小数点の位置に基づいて数値を揃えます。

select to_varchar(f, '999,999.999'), to_varchar(f, 'S999,999.999') from sample_numbers;

出力は次のようになります。

+------------------------------+-------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'S999,999.999') |
+==============================+===============================+
|        1.2                   |       +1.2                    |
+------------------------------+-------------------------------+
|      123.456                 |     +123.456                  |
+------------------------------+-------------------------------+
|    1,234.56                  |   +1,234.56                   |
+------------------------------+-------------------------------+
| -123,456.789                 | -123,456.789                  |
+------------------------------+-------------------------------+

この例は、 FM (Fillモード)修飾子を使用して「fill」モードから「compact」モードに切り替えた場合、つまり数字を揃える先頭の文字を削除した場合の動作を示しています。

select  to_varchar(f, '999,999.999'), to_varchar(f, 'FM999,999.999') from sample_numbers;

出力は次のようになります。

+------------------------------+--------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'FM999,999.999') |
+==============================+================================+
|        1.2                   | 1.2                            |
+------------------------------+--------------------------------+
|      123.456                 | 123.456                        |
+------------------------------+--------------------------------+
|    1,234.56                  | 1,234.56                       |
+------------------------------+--------------------------------+
| -123,456.789                 | -123,456.789                   |
+------------------------------+--------------------------------+

次の例は、指数表記で数値を表示する方法を示しています。

select to_char(1234, '9d999EE'), 'will look like', '1.234E3';

出力は次のようになります。

+--------------------------+------------------+-----------+
| TO_CHAR(1234, '9D999EE') | 'WILL LOOK LIKE' | '1.234E3' |
+==========================+==================+===========+
| 1.234E3                  |  will look like  |  1.234E3  |
+--------------------------+------------------+-----------+

これは、出力にリテラルを含める方法を示しています。リテラル部分は二重引用符で囲まれています(二重引用符は文字列を区切る一重引用符内)。

select to_char(12, '">"99"<"');

出力は次のようになります。

+-------+
| > 12< |
+-------+

入力例

これらの例は、入力に形式モデルを使用する方法を示しています。

次の例は、「0」と「9」を使用して数字の形式を指定することの違いを強調して、いくつかの単純な入力操作を示しています。

フォーマッタとしての数字「9」は、空白または「欠落」先行数字を受け入れます。フォーマッタとしての数字「0」は、空白または欠落している先行ゼロを受け入れません。

-- All of the following convert the input to the number 12,345.67.
SELECT TO_NUMBER('012,345.67', '999,999.99', 8, 2);
SELECT TO_NUMBER('12,345.67', '999,999.99', 8, 2);
SELECT TO_NUMBER(' 12,345.67', '999,999.99', 8, 2);
-- The first of the following works, but the others will not convert.
-- (They are not supposed to convert, so "failure" is correct.)
SELECT TO_NUMBER('012,345.67', '000,000.00', 8, 2);
SELECT TO_NUMBER('12,345.67', '000,000.00', 8, 2);
SELECT TO_NUMBER(' 12,345.67', '000,000.00', 8, 2);

これは、2つの数値形式(-### または ###-)のいずれかを受け入れる方法を示しています。

-- Create the table and insert data.
create table format1 (v varchar, i integer);
insert into format1 (v) values ('-101');
insert into format1 (v) values ('102-');
insert into format1 (v) values ('103');

-- Try to convert varchar to integer without a
-- format model.  This fails (as expected)
-- with a message similar to:
--    "Numeric value '102-' is not recognized"
update format1 set i = TO_NUMBER(v);

-- Now try again with a format specifier that allows the minus sign
-- to be at either the beginning or the end of the number.
-- Note the use of the vertical bar ("|") to indicate that
-- either format is acceptable.
update format1 set i = TO_NUMBER(v, 'MI999|999MI');
select i from format1;