SQLのcastについてまとめています。
castは、データ型の変換をおこなう関数です。ただし、データベースによって使い方に差異があります。
SQLのcastの構文
castは、値を明示的に型変換する関数です。以下の構文で使用します。
cast(値 as データ型)
castは、データベースによって扱うことができるデータ型が異なります。
Oracleのcastは、変換失敗時のデフォルトを指定可能
Oracleでは、以下のデータ型のcastが可能です。
- BINARY_FLOAT、BINARY_DOUBLE
- CHAR、VARCHAR2
- NUMBER/INTEGER
- DATETIME/INTERVAL
- RAW
- ROWID、UROWID
- NCHAR、NVARCHAR2
ただし、型の組み合わせによっては変換できないものもあります。LONG型、LONG RAW型、Oracleが提供する型はサポートされていません。
Oracleでは、以下の書式で、変換できなかった場合のデフォルト値をON CONVERSION ERROR指定することが可能です。
CAST ( expr AS type_name ↵[ DEFAULT def_val ON CONVERSION ERROR ] ↵[ , format[, nls_param]] )
以下のSQLは、２００を整数に変換できなかった場合、０をデフォルト値に指定します。
SELECT CAST(200
AS NUMBER
DEFAULT 0 ON CONVERSION ERROR)
FROM DUAL;
日付を表す文字列をTIMESTAMP型に変換するSQLは、以下のように記述できます。日付の変換方式の指定が可能です。
SELECT CAST('1999-12-01 11:00:00 -8:00'
AS TIMESTAMP WITH TIME ZONE
DEFAULT '2000-01-01 01:00:00 -8:00' ON CONVERSION ERROR,
'YYYY-MM-DD HH:MI:SS TZH:TZM',
'NLS_DATE_LANGUAGE = American')
FROM DUAL;
また、MULTISETを使ってサブクエリをキャストすることも可能です。
SELECT s.custno, s.name,
CAST(MULTISET(SELECT ca.street_address,
ca.postal_code,
ca.city,
ca.state_province,
ca.country_id
FROM cust_address ca
WHERE s.custno = ca.custno)
AS address_book_t)
FROM cust_short s
ORDER BY s.custno;
参考）OracleのCAST
SQL Serverのcast where句でキャスト後の値を条件式に使う例
SQL Serverでは、以下の型の変換が可能です。
- binary,varbinary,char,varchar,nchar,nvarchar
- datetime,smalldatetime,date,time,datetimeoffset,datetime2
- decimal,numeric,float,real,bigint,int(INT4),smallint(INT2),tinyint(INT1)
- money,smallmoney
- bit
- timestamp,uniquidentifier,image,ntext,text,sql_variant,xml,CLR UDT,hierarchyid。
参考）CAST および CONVERT (Transact-SQL) – SQL Server | Microsoft Docs
以下のSQLは、表示価格（ListPrice）をint型に変換した結果、33で始まるデータのProductName（製品名）、ListPrice（表示価格）をProduction.Productテーブルから取得するSQLです。
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int) LIKE '33%'; GO
小数点以下の数値は四捨五入されるため、ListPriceが32.6のデータはintにキャストすることで33となり、対象データとなります。
PostgreSQLのcastは、変換方法を事前に定義可能
create castで新しいキャストを定義可能。
cast(12345 as varchar)は、12345::varcharという記述も可能。castを使うよりもスッキリします。。
CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;
上記のSQLは、関数int4(bigint)を使用したbigint型からint4型への代入キャストを作成します。なお、このキャストはすでにシステムに定義済みです。
AS ASSIGNMENTのかわりに、AS INPLICITを使用すると任意のコンテキストでの暗黙キャストが可能になります。な、AS INPLICITはPostgreSQL独自の拡張です。
MySQLのcast 旧バージョンではcastの過程で切り捨てが発生する可能性あり
MySQLでは、BINARY,CHAR、DATE、DATETIME、DECIMAL、SIGNED、TIME、UNSIGNEDへのキャストが可能。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.10 キャスト関数と演算子
mysql> SELECT CAST(1-2 AS UNSIGNED)
-> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
-> -1
UNSIGNED（符号なし）と、SIGNED（符号付き）で結果が変わります。
TIMESTAMPから、SIGNED（符号付き整数）にCASTする場合、MySQL5.6.4以降ではこうなります。
mysql> select cast(TIMESTAMP '2020-03-20 12:01:01' as SIGNED); +-------------------------------------------------+ | cast(TIMESTAMP '2020-03-20 12:01:01' as SIGNED) | +-------------------------------------------------+ | 20200320120101 | +-------------------------------------------------+ 1 row in set (0.00 sec)
MySQL5.6.4以前では、cast結果は2020となり数値でない部分（ハイフン）で切り捨てが発生していました。
まとめ
- castは、データ型を別の型に明示的に変換する関数。
- Oracleは、変換失敗時のデフォルト値を指定可能。
- PostgreSQｌは、事前に変換方式をcreate castで定義可能。
