CAST (SQL)

文字列

• CAST(expression AS [CHAR | CHARACTER | VARCHAR | NCHAR | NVARCHAR]) 数値または文字列の式を文字列データ型に変換します。これらのデータ型はすべて %Library.StringOpens in a new tab にマッピングされます。

  • CHAR、CHARACTER、NCHAR は同等のデータ型であり、既定の長さは 1 文字です。

  • VARCHAR と NVARCHAR は同等のデータ型であり、既定の長さは 30 文字です。

  以下の文からは、VARCHAR のデータ型として Name (文字列)、Age (数値)、DOB (日付値) が返されます。

  SELECT DISTINCT
    CAST(Name AS VARCHAR) AS VarCharName,
    CAST(Age AS VARCHAR) AS VarCharAge,
    CAST(DOB AS VARCHAR) AS VarCharDOB
  FROM Sample.Person

  

  この文は文字列値を単一の文字列にキャストし、元の文字列にあるその他の文字を切り捨てます。

  SELECT CAST('True' AS CHAR) -- T

  

  例 ： 文字列値のキャスト

• CAST(expression AS [CHAR VARYING | CHARACTER VARYING]) は式を変換し、元の値にある文字と同じ個数の文字を返します。

  この文は、浮動小数点で表現したπ値を文字列値として返します。この文字列には、πの浮動小数点精度の桁数と同じ個数の文字があります。

  SELECT CAST({fn PI()} AS CHAR VARYING) AS StringPi -- 3.141592653589793238
  

  

  例 ： 文字列値のキャスト

• CAST(expression AS [CHAR(length) | CHARACTER(length) | VARCHAR(length) | CHAR VARYING(length) | CHARACTER VARYING(length)]) は length で指定された文字数の文字列に式を変換します。余分な文字は切り捨てられます。

  以下の文は、入力文字列式の先頭 8 文字で構成した文字列を返します。

  SELECT CAST('Grabscheid,Alfred N.' AS CHAR(8)) -- Grabsche

  

  例 ： 文字列値のキャスト

数値

• CAST(expression AS [INT | INTEGER | BIGINT | SMALLINT | TINYINT]) INT、INTEGER、BIGINT、SMALLINT、または TINYINT のデータ型に式を変換します。これらのデータ型では、小数部が切り捨てられます。

  以下の例では、浮動小数点ではなく、整数で平均値が求められます。CAST によって数値が切り捨てられるので、平均年齢 42.9 は 42 になります。

  SELECT DISTINCT AVG(Age) AS AvgAge,
     CAST(AVG(Age) AS INTEGER) AS IntAvgAge
        FROM Sample.Person

  

  例 ： 数値のキャスト

• CAST(expression AS [DEC | DECIMAL | NUMERIC]) は DEC、DECIMAL、または NUMERIC のデータ型に式を変換します。これらのデータ型では、元の値の桁数が保持されます。InterSystems IRIS では $DECIMAL 関数を使用してこれらのデータ型が変換されます。この関数は $DOUBLE 値を $DECIMAL 値に変換します。これらのデータ型は %Library.NumericOpens in a new tab データ型にマッピングされます。

  以下の文は、1 ラジアンの正弦値である浮動小数点値を小数として返します。

  SELECT CAST({fn SIN(1)} AS DECIMAL) AS DecimalValue -- 0.841470984807897

  

  例 ： 数値のキャスト

• CAST(expression AS [DEC(precision,scale) | DECIMAL(precision,scale) | NUMERIC(precision,scale)]) はデータ型の精度と記数法を指定します。

  • precision は、データ型で指定できる桁数の合計値を指定します。precision を指定すると、CAST から返される値に影響はありませんが、定義した型の一部として保持されます。

  • scale データ型の小数点以下桁数を指定します。CAST では、この指定した桁数で数値が四捨五入されます。

  以下の文は、1 ラジアンの正弦値を小数点以下 4 桁の小数値として返します。

  SELECT CAST({fn SIN(1)} AS DECIMAL(8,4)) AS ScaledDecimalValue -- 0.8415

  

  例 ： 数値のキャスト

• CAST(expression AS DOUBLE) は、IEEE 浮動小数点標準に準拠する DOUBLE データ型に式を変換します。詳細は、ObjectScript の "$DOUBLE" 関数を参照してください。

  以下の文は、倍精度浮動小数点値として 1 ラジアンの正弦値を返します。

  SELECT CAST({fn SIN(1)} AS DOUBLE) AS DoubleValue -- .84147098480789650487

  

  例 ： 数値のキャスト

• CAST(expression AS [MONEY | SMALLMONEY]) は、通貨の数値データ型である MONEY または SMALLMONEY に式を変換します。通貨データ型の小数点以下桁数は必ず 4 です。

  以下の文は、通貨値として整数 10 を返します。

  SELECT CAST(10 AS MONEY) AS MoneyValue -- 10.0000 (Display Mode)
  

  

日付と時刻

• CAST(expression AS DATE) は、書式設定された日付式を DATE データ型に変換します。InterSystems IRIS では、コンテキストに応じて以下の形式で日付が表現されます。

  • ロケールに合わせた表示日付形式 (例 ： mm/dd/yyyy)

  • ODBC 日付形式 (yyyy-mm-dd)

  • $HOROLOG 整数日付ストレージ形式 (nnnnn)

  $HOROLOG の日付部分の値は、数値文字列ではなく、整数として指定する必要があります。

  以下の文は、文字列を DATE データ型にキャストします。

  SELECT CAST('1936-11-26' As DATE) AS StringToDate

  

  例 ： フォーマットされた文字列の日付へのキャスト

• CAST(expression AS TIME) は、書式設定された時刻式を TIME データ型に変換します。InterSystems IRIS では、コンテキストに応じて以下の形式で時刻が表現されます。

  • ロケールに合わせた表示時刻形式 (例 ： mm/dd/yyyy)

  • ODBC 日付形式 (hh:mm:ss)

  • $HOROLOG 整数時刻ストレージ形式 (nnnnn)

  $HOROLOG の日付部分の値は、数値文字列ではなく、整数として指定する必要があります。

  以下の文は、文字列を TIME データ型にキャストします。

  SELECT CAST('14:33:45.78' AS TIME) AS StringToTime

  

  例 ： フォーマットされた文字列の時刻へのキャスト

• CAST(expression AS [TIMESTAMP | DATETIME | SMALLDATETIME]) は、日付とタイムスタンプを YYYY-MM-DD hh:mm:ss.nnn 形式で表現します。この値は、ObjectScript の $ZTIMESTAMP 特殊変数に対応します。

  以下の文は、日付と時刻の文字列を TIMESTAMP データ型にキャストします。

  SELECT CAST('November 26, 1936 14:33:45.78' AS TIMESTAMP) AS StringToTS

  

  例 ： フォーマットされた文字列のタイムスタンプへのキャスト

• CAST(expression AS POSIXTIME) は、日付とタイムスタンプを表現する式を、エンコードした 64 ビットの符号付き整数に変換します。このエンコード形式の詳細は、"日付、時刻、PosixTime、およびタイムスタンプのデータ型" を参照してください。

  以下の文は、日付と時刻の文字列を POSIXTIME データ型にキャストします。

  SELECT CAST('November 26, 1936 14:33:45.78' AS POSIXTIME) AS StringToPosix
  

  

  例 ： POSIXTIME への日付のキャスト

ビット値

• CAST(expression AS BIT) は、BIT データ型の単一のブーリアン値に式を変換します。

  以下の文は、それぞれ 1 と 0 の BIT 値を返します。

  SELECT
    CAST('1' AS BIT) As BitTrue,
    CAST('0' AS BIT) As BitFalse

  

  例 ： ビット値のキャスト

バイナリ値

• CAST(expression AS [BINARY | BINARY VARYING | VARBINARY]) は、%Library.BinaryOpens in a new tab (xDBC データ型 BINARY) にマッピングされる 3 つのデータ型のいずれかに式を変換します。

  • BINARY の既定の長さは 1 です。

  • BINARY VARYING および VARBINARY の既定の長さは 30 です。

  CAST では、バイナリ値へのキャストでデータが変換されるのではなく、指定した length. の長さに値が切り詰められます。

• CAST(expression AS [BINARY(length) | BINARY VARYING(length) | VARBINARY(length)]) は、返すバイナリ・データ型の最大文字長を設定します。

一意の識別子

• CAST(expression AS GUID) GUID は、データ型 %Library.UniqueIdentifierOpens in a new tab の 36 文字の値を表します。37 文字以上の expression を指定した場合、CAST は expression の最初の 36 文字を返します。GUID 値を生成するには、%SYSTEM.Util.CreateGUID()Opens in a new tab メソッドを使用します。

expression

SQL 式。一般的には、テーブルの中で変換対象となるリテラルまたはデータ・フィールドです。

length

キャスト後に返す最大文字数を示す整数です。

• length が expression の長さより短い場合、返されるデータは先頭から length 文字に切り詰められます。

• length が expression の長さより長い場合、CAST は切り捨てもパディングも実行しません。

precision

キャストしたデータ型で返される合計桁数の最大値です (整数で指定)。precision は、定義されたデータ型の一部として保持されますが、CAST で返される値には影響しません。

精度の詳細は、"精度と小数桁数" を参照してください。

scale

キャストしたデータ型で返される小数点以下桁数の最大値です (整数で指定)。返される値が scale の桁数で四捨五入されます。

• scale = 0 を指定すると数値が整数に四捨五入されます。

• scale = -1 を指定すると数値が整数に切り捨てられます。

• scale を指定しないと、小数点以下桁数は既定値の 15 になります。

キャストした値の桁数よりも scale が大きい場合、表示モードでは適切な個数のゼロが返された値の末尾に付加されますが、論理モードと ODBC モードではこれらの桁は切り捨てられます。

小数点以下桁数の詳細は、"精度と小数桁数" を参照してください。

文字列値のキャスト

文字列をさまざまな数値、日付、時刻、文字列の値にキャストできます。

文字列間のキャスト

文字列を他の文字データ型にキャストすると、1 文字、先頭の length 文字、または文字列全体が返されます。

SELECT
  CAST('Hello World' AS CHAR) AS StringToChar, -- H
  CAST('Hello World' AS CHAR(5)) AS StringToCharLength, -- Hello
  CAST('Hello World' AS CHAR VARYING) AS StringToCharVary -- Hello World

キャストの前に、InterSystems SQL によって埋め込み引用符文字および文字列連結が解決されます。先頭の空白や末尾の空白は削除されます。

SELECT
  CAST('Can''t' AS VARCHAR) AS EmbeddedQuote, -- Can't
  CAST('Can'||'not' AS VARCHAR) AS StringConcatenation -- Cannot

数値型への文字列のキャスト

文字列を数値型にキャストすると、InterSystems SQL は 1 桁のゼロ (0) を返します。

SELECT CAST('Hello World' AS DOUBLE) AS StringToNumeric -- 0

以下の例では、CAST 関数を使用して Name (文字列) をさまざまな数値データ型に変換した結果を示します。どの場合でも、返される値は 0 (ゼロ) です。

SELECT DISTINCT 
  CAST(Name As INT) As IntName,
  CAST(Name As SMALLINT) As SmallIntName,
  CAST(Name As DEC) As DecName,
  CAST(Name As NUMERIC) As NumericName
FROM Sample.Person

フォーマットされた文字列の日付へのキャスト

'yyyy-mm-dd' 形式の文字列を DATE データ型にキャストできます。これは、ODBC 日付形式に対応した文字列形式です。InterSystems SQL では、入力した式に対して値と範囲が確認されます。各要素の内容を以下に示します。

• 年 (yyyy) には、00001 以上 9999 以下の数値を指定する必要があります。

• 月 (mm) には、01 以上 12 以下の数値を指定する必要があります。

• 日 (dd) には、該当する月で有効な数値を指定する必要があります。

InterSystems SQL は欠落している先頭のゼロを挿入します。以下に例を示します。

SELECT CAST('2022-3-1' AS DATE) AS DateValue -- 03/01/2022 (Display Mode)

日付が無効な場合は 1840-12-31 (論理日付 0) を返します。例えば、2/29 はうるう年の場合にのみ有効です。

SELECT CAST('2021-02-29' AS DATE) AS InvalidDate -- 12/31/1840 (Display Mode)

表示モードおよびロケールの日付表示形式によってキャストの表示が決まります。例えば、'2004-11-23' が '11/23/2004' と表示されます。

埋め込み SQL は、対応する $HOROLOG 日付整数としたキャストを返します。無効な ODBC 日付や数値ではない文字列を DATE にキャストすると、論理モードでは 0 とされます。日付 0 は 1840–12–31 として表示されます。

フォーマットされた文字列の時間へのキャスト

'hh:mm'、'hh:mm:ss'、'hh:mm:ss.nn' (length 秒の小数点以下桁数は任意) 形式の文字列は TIME データ型にキャストできます。これは、ODBC 時刻形式に対応した文字列形式です。InterSystems SQL では、入力した式に対して値と範囲が確認されます。各要素の内容を以下に示します。

• 時間 (hh) には、00 以上 23 以下の数値を指定する必要があります。

• 分 (mm) には、00 以上 59 以下の数値を指定する必要があります。

• 秒 (ss) には、00 以上 60 未満の数値を指定する必要があります。秒に小数部は使用できますが、切り捨てられます。

InterSystems SQL は欠落しているゼロを追加します。以下に例を示します。

SELECT
  CAST('2:45' AS TIME) AS StringToTime1, -- 02:45:00 (Display Mode)
  CAST('2:45:59' AS TIME) AS StringToTime2, -- 02:45:59 (Display Mode)
  CAST('2:45:59.98' AS TIME) AS StringToTime3 -- 02:45:59.98 (Display Mode)

時刻が無効な場合は 00:00:00 を返します (論理時刻 0)。

SELECT CAST('11:52:60' AS TIME) AS InvalidTime -- 00:00:00 (Display Mode)

埋め込み SQL は、対応する $HOROLOG 時刻整数としてキャストを返します。無効な ODBC 時刻や数値ではない文字列を TIME にキャストすると、論理モードでは 0 とされます。時刻 0 は 00:00:00 として表示されます。

フォーマットされた文字列のタイムスタンプへのキャスト

有効な日付と時刻、有効な日付、または有効な時刻で構成した文字列は TIMESTAMP データ型にキャストできます。日付部分はさまざまな形式で指定できます (TO_TIMESTAMP 関数の説明を参照)。ここで得られるタイムスタンプは、YYYY-MM-DD hh:mm:ss 形式で表されます。

SELECT
  CAST('1 MAR 2022 1:33pm' AS TIMESTAMP) As DateToTS1, -- 2022-03-01 13:33:00
  CAST('3/1/2022 13:33:00' AS TIMESTAMP) As DateToTS2 -- 2022-03-01 13:33:00 

CAST はフォーマットされた日付を以下のように解決します。

• 省略されている日付部分を 1841-01-01 (論理日付1) に設定します。

• 省略されている時刻部分を 00:00:00 に設定します。

• 月および日の先頭のゼロ (省略されている場合) を挿入します。

秒の小数部を指定している場合は、それをピリオド (.) またはコロン (:) で始めることができます。

• ピリオドは標準の小数部であることを示します。以下に例を示します。

  • 12:00:00.4 の秒数は 10 分の 4 秒

  • 12:00:00.004 の秒数は 1000 分の 4 秒

• コロンは、次に続く値が 1000 分の 1 秒単位であることを示します。12:00:00:4 の秒数は 1000 分の 4 秒です。コロンの後に続けることができる桁数は 3 に制限されています。

ある時刻データ型の文字列を別の時刻データ型にキャストすることもできます。以下の例では、文字列を TIME データ型にキャストし、その結果を TIMESTAMP データ型にキャストします。日付は現在のシステム日付に設定されます。

SELECT CAST(CAST('14:33:45.78' AS TIME) As TIMESTAMP) AS TimeToTstamp

日付値のキャスト

日付は、文字列データ型、数値データ型、または別の日付データ型にキャストできます。

文字列への日付のキャスト

日付を文字列データ型にキャストすると、日付全体、またはキャスト先のデータ型で決まる長さの日付が返されます。例えば、以下のクエリでは、現在の日付が "yyyy-mm-dd" として返されず、"yyyy-" のみが返されます。

SELECT CAST(CURRENT_DATE AS CHAR(5)) AS TruncatedDate

CHAR VARYING および CHARACTER VARYING の両データ型では、完全な表示形式が返されます。

SELECT CAST(CURRENT_TIME AS CHAR VARYING) AS FullDate

日付が mm/dd/yyyy などの異なる形式で表示される場合、文字列データ型は ODBC 日付形式 (yyyy-mm-dd) で日付を返します。以下に例を示します。

SELECT CAST(TO_DATE('03/01/2022','MM/DD/YYYY') AS VARCHAR) AS DateFormat -- 2022-03-01

数値型への日付のキャスト

日付を数値データ型にキャストすると、その日付の $HOROLOG 値が返されます。これは、1840 年 12 月 31 日を 0 とし、そこから起算した日数を表す整数値です。以下に例を示します。

SELECT CAST(TO_DATE('01 MAR 2022') AS DECIMAL) AS DateToNumeric -- 66169

POSIXTIME への日付のキャスト

日付を POSIXTIME データ型にキャストすると、エンコードされた 64 ビットの符号付き整数としてタイムスタンプが返されます。日付には時刻部分がないため、00:00:00 としてエンコードされたタイムスタンプに時刻部分が指定されます。

SELECT CAST(CURRENT_DATE AS POSIXTIME) As PosixDate

CAST は日付を検証します。expression の値が有効な日付でない場合は、SQLCODE -400 エラーが発行されます。

TIMESTAMP、DATETIME、または SMALLDATETIME への日付のキャスト

日付を TIMESTAMP、DATETIME、または SMALLDATETIME のいずれかのデータ型にキャストすると、YYYY-MM-DD hh:mm:ss 形式のタイムスタンプが返されます。日付には時刻部分がないため、返されるタイムスタンプの時刻部分は常に 00:00:00 です。CAST は日付を検証します。expression の値が有効な日付でない場合は、SQLCODE -400 エラーが発行されます。

以下の例では、DATE データ型の列を TIMESTAMP にキャストします。比較のために POSIXTIME データ型も取り上げています。

SELECT TOP 5
  DOB,
  CAST(DOB AS TIMESTAMP) AS TStamp,
  CAST(DOB AS POSIXTIME) AS Posix
FROM Sample.Person

数値のキャスト

数値は、数値データ型または文字データ型にキャストできます。少ない桁数の値に数値をキャストすると、その数値は四捨五入ではなく、切り捨てられます。

SELECT
  CAST(98.765 AS INT) AS TruncatedInt, -- 98
  CAST(98.765 AS CHAR) AS TruncatedChar1, -- 9
  CAST(98.765 AS CHAR(4)) AS TruncatedChar2 --98.7

負数を CHAR にキャストすると、負の符号だけが返されます。分数を CHAR にキャストすると、小数点だけが返されます。

SELECT
  CAST(-50 AS CHAR) AS Negative, -- negative sign: -
  CAST(1/4 AS CHAR) AS Fraction -- decimal point: .

数値には以下の値を指定できます。

• 0 から 9 までの数字

• 小数点

• 1 つまたは複数の先行符号 (+ または –)

• 指数記号 (E または e の文字) とそれに続く 1 つ以下の正または負の符号

キャストを実行する前に、指数演算の実行、複数の符号の解決、先頭の正符号、末尾の小数点、先頭または末尾のゼロの削除によって、数値がキャノニック形式に解決されます。以下に例を示します。

SELECT
  CAST(1e2 AS DECIMAL(6,2)) AS PositiveExponent, -- 100.000
  CAST(1e-2 AS DECIMAL(6,2)) AS NegativeExponent, -- 0.01
  CAST(+1000 AS DECIMAL(6,2)) AS LeadingSign, -- 1000.00
  CAST(-+1000 AS DECIMAL(6,2)) AS MultipleSigns, -- -1000.00
  CAST(00.100 AS DECIMAL(6,2)) AS LeadingTrailingZeros -- 0.10

2 つ続く負の符号はコメント文字として扱われます。数値の中に負の符号を 2 つ続けて記述すると、そのコード行の以降の部分はコメントとして扱われます。数値に桁区切り文字 (コンマ) を入れることはできません。詳細は、"リテラル" を参照してください。

数値はさまざまな数値型に変換できます。以下の例では、CAST で浮動小数点数のπをさまざまな数値データ型に変換しています。整数データ型には切り捨てが適用されます。

SELECT 
   CAST({fn PI()} As INTEGER) As IntegerPi, -- 3
   CAST({fn PI()} As SMALLINT) As SmallIntPi, -- 3
   CAST({fn PI()} As DECIMAL) As DecimalPi, -- 3.141592653589793
   CAST({fn PI()} As NUMERIC) As NumericPi, -- 3.141592653589793
   CAST({fn PI()} As DOUBLE) As DoublePi -- 3.1415926535897931159

この例では、precision と scale の値が解析され、CAST によって返される値が変更されます。

SELECT 
   CAST({fn PI()} As DECIMAL) As DecimalPi, -- 3.141592653589793 
   CAST({fn PI()} As DECIMAL(6,3)) As DecimalPiPS -- 3.142

数値を日付データ型または時刻データ型にキャストすると、SQL ではゼロ (0) として表示されます。日付または時刻としてキャストした数値は、埋め込み SQL から ObjectScript に渡すと、対応する $HOROLOG 値として表示されます。

ビット値のキャスト

expression を 0 または 1 として返すには、それをビット値にキャストします。

expression が以下のいずれかの値であると、CAST から 1 (真) が返されます。

• 1 などのゼロ以外の数値。

• "TRUE"、"True"、"true" など、単語 true の大文字と小文字を組み合わせた表記。省略形の "T" を使用することはできません。

以下の CAST 演算からはすべて 1 が返されます。

SELECT CAST(1 AS BIT) AS One, 
  CAST(7 AS BIT) AS Num,
  CAST(743.6 AS BIT) AS Frac,
  CAST(0.3 AS BIT) AS Zerofrac,
  CAST('tRuE' AS BIT) AS TrueWord

expression が以下のいずれかの値であると、CAST から 0 (偽) が返されます。

• 単語 "true" とその大文字と小文字のさまざまな組み合わせ以外の、数値ではない任意の値

• 空の文字列 ('')

• 数値 0

以下の CAST 演算からはすべて 0 が返されます。

SELECT CAST(0 AS BIT) AS Zero,
  CAST('FALSE' AS BIT) AS FalseWord,
  CAST('T' AS BIT) AS T,
  CAST('F' AS BIT) AS F,
  CAST(0.0 AS BIT) AS Zerodot,
  CAST('' AS BIT) AS EmptyString

NULL および空文字列値のキャスト

NULL はどのデータ型にキャストしても、NULL として返されます。

SELECT CAST(NULL AS DATE) AS NullValue

空の文字列 ('') のキャスト結果はデータ型によって異なります。