この記事は、以前の記事のフォローアップとして、SQL Cast と SQL Convert 関数について説明する試みです。この記事では、SQL Date、SQL Coalesce、SQL Union、SQL Join、SQL Like、SQL String などのいくつかの SQL のヒントについて説明しました。
時には、異なるSQLデータタイプ間でデータを変換する必要があります。 データを扱うだけでなく、データの変換に使用できる組み込み関数もあります。 そこで、SQLの変換関数である「SQL CAST」と「SQL CONVERT」について詳しく見ていきましょう。
はじめに
SQL Serverデータベースのデータ間で演算や比較、変換を行うには、それらの値のSQLデータ型が一致していなければなりません。 SQLデータ型が異なる場合、タイプキャスティングと呼ばれるプロセスを経ることになります。 このプロセスにおけるSQLデータ型の変換は、暗黙的なものと明示的なものがあります。
データ型の変換機能は、対象となるオブジェクトやシステムのデータ標準を満たすために特別に使用されるのが一般的です。 私たちは異質なデータを扱う世界にいます。
注: データ管理の高レベルな概念のウォークスルーについては、この記事「SQL string functions for Data Munging (Wrangling)」で説明しています。
計算や変換、処理のために、あるいは宛先のデータ形式に合わせるために、データのデータ型を他のデータ型に変換する必要がある場合があります。
暗黙的および明示的な SQL 変換
本質的には同じだがデータ型が異なる 2 つの値を扱うとき、舞台裏では、データベース エンジンが計算を進める前に、低いデータ型の値を高いデータ型に変換します。 このタイプは暗黙の変換と呼ばれています。 一方、明示的な変換では、SQL CAST関数やSQL CONVERT関数を呼び出してデータ型を変更します。 例としては、SQLの日付形式の概要、DateDiff SQL関数、DateAdd SQL関数などの記事を参照してください。
構文を示します。
|
1
2
divdivdiv 3div
4
|
CAST
CAST ( exp AS datatype )
CONVERT
CONVERT ( datatype , expression )
|
exp
有効な式の引数を定義する
datatype
対象となるデータ型の詳細を指定する
len
対象となるデータ型の長さを指定する。 これはデフォルトではオプションのパラメータです。
style
式を目的の出力に変換するSQL CONVERT関数で使用される整数値です
SQL ConvertとCastデータの変換チャート
以下の変換チャートは、SQL CONVERTのクイックリファレンスシートです。
Example 1: SQL Implicit conversion with numbers
以下の例では、2つの乱数「5」と「212345」を加算し、結果を表示しています。
|
1
2
|
DECLARE @a int=5, b bigint=212345
SELECT @a, @b, a+@b
|
2つの値が異なるデータタイプであることがわかります。 データベースエンジンは、加算を行う前にint値をbigintに暗黙的に変換しました。 そして、それらが同じデータ型になったので、加算操作を処理することができました。
Example 2: SQL Implicit conversion with characters
次の例では、2つの文字データ型を追加します。 変数@aの文字データ型を作成して値「5」を割り当て、@bには値「2」を割り当ててみましょう。
|
1
2
|
DECLARE @a char=’5′, a char=’5′, @b char=’2′
SELECT @a, b, @a+@b
|
出力では、まず、変数’a’と’b’の値が得られ、次に、両方の値を合わせた計算結果が得られています。
例 3: SQL Cast を使用して明示的な変換を行う方法
次の例では、あるデータ型から別のデータ型に強制的に変換する方法を見てみましょう。 上の例を参考にして、入力値を数値型に変換し、それらの値を追加してみましょう。
|
1
2
|
DECLARE @a char=’5′, b char=’2′
SELECT @a,@b, cast(@a as int)+ cast(@b as int)
|
SQLのCAST関数は2つの引数を取ります。 まず、処理したいデータ（この場合、データは’5’と’2’で、charフィールドです）、そして、それをどのように処理したいか、つまり、どのようにCASTしたいかです。
出力では、入力された文字の値が変換され、それらの値が加算され、結果として整数型になっていることがわかります。
例4: TRY_CASTを使用した高度なエラー処理
次の例では、SQL cast関数とSQL try_cast関数を使用して、データ型の値をさらに分解してみます。
以下のSQLでは、文字列値「123」がBIGINTに変換されています。 データの性質上、変換することができました。 2番目の例を見てみましょう。文字列値’xyz’は、SQLのキャスト関数のための式で使用されています。 その際、「Error Converting data type varchar to bigint」というエラーが表示されています。 様々なデータソースを使ってデータ分析を行っていると、通常、ソースデータにこのようなタイプの値が含まれています。 そこで、このようなシナリオを処理するために、SQLのtry_cast関数を使用することができます。 SQLの3行目では、入力値として文字列を渡していますが、実行後、エラーは発生しませんでしたが、出力としてNULL値を取得してしまいました。 このNULL値は、SQL ISNULL関数やSQL coalesce関数を使ってさらに簡略化することができます。
注: 詳細は、Using the SQL Coalesce function in SQL Serverを参照してください。
|
1
2
3
4
5
6divdiv
7
|
SELECT TRY_CAST(‘123’ AS BIGINT)。
GO
SELECT CAST(‘xyz’ AS BIGINT);
GO
SELECT TRY_CAST(‘xyz’ AS BIGINT);
GO
SELECT ISNULL(TRY_CAST(‘xyz’ AS BIGINT), 99);
|
例5: CONVERT関数でのスタイルコードの説明
以下の例では、hiredateフィールドをSQL変換関数の異なる利用可能なスタイルに変換しています。 SQL変換関数は、まずデータ型とコンマから始まり、次に式が続きます。 Dateの場合は、SQLスタイルコードと呼ばれるもう1つの引数を指定することができます。 これは、SQLスタイルコードと呼ばれています。これらのリストは、CAST and CONVERT (Transact-SQL) technical documentationの記事に掲載されています。
|
1
2
3
4
5
6
7
8
9
|
SELECT hiredate,
CONVERT(CHAR(50), hiredate),
CONVERT(CHAR(50), hiredate, 1) ‘S-1’,
CONVERT(CHAR(50), hiredate, 101) ‘S-101’,
CONVERT(CHAR(50), hiredate, 102) ‘S-102’,
CONVERT(CHAR(50), hiredate, 103) ‘s-103’,
CONVERT(CHAR(50), hiredate, 104) ‘s-104’,
CONVERT(CHAR(50), hiredate, 105) ‘s-105’
FROM HumanResources.Employee;
|
先に前述のSQLを実行してみましょう。 ここでは、SQL変換関数内のスタイル・パラメータを使って、異なる出力が表示されているのがわかります。 hiredateフィールドが日付フィールドを異なるスタイルに変換しているのがわかります。
例 6: CASTとCONVERTの違いと共通点
以下の例では、以下のT-SQLでCASTとCONVERTを使用して、実行時間を比較しています。
次のT-SQLを実行してみましょう。SQLでSQL CAST関数を使用してみましょう
|
1
2
3
4
5
6
7
8
9
10
11
|
div div
SET STATISTICS TIME ON
WITH temp
AS (SELECT CAST(GETDATE() AS DATE) AS cast_date,
0 AS n
UNION ALL
SELECT CAST(GETDATE() + n AS DATE) AS cast_date,
n + 1
FROM temp
WHERE n < 1000000)
SELECT MAX(cast_date)
FROM temp OPTION(MAXRECURSION 0);
|
さて。 同じT-SQLをSQL CONVERT関数を使用して実行してみましょう。
|
1
2
3
4
5
6
7
8
9
10
11
|
SET STATISTICS TIME ON
WITH temp
AS (SELECT CONVERT(DATE, GETDATE(), 20) AS convert_date,
0 AS n
UNION ALL
SELECT CONVERT(DATE, GETDATE() + n, 20) AS convert_date,
n + 1
FROM temp
WHERE n < 1000000)
SELECT MAX(convert_date)
FROM temp OPTION(MAXRECURSION 0);
|
簡単なヒントです。
- CASTは純粋にANSI-SQL Standardです。
- CAST は純粋に ANSI-SQL 標準ですが、CONVERT は SQL Server 固有の関数で、Oracle には to_char や to_date があります
- CAST はその移植性と使いやすさから、すべてのデータベース製品で主に使用されています
- SQL Cast と SQL Convert 関数の間で、クエリの実行に関して大きな違いはありません。 実行時間に若干の違いが見られますが、これは SQL CAST からネイティブの SQL CONVERT 関数への内部変換によるものですが、CONVERT 関数には「Style-code」というオプションがあり、日付と時刻、小数、金額のさまざまな組み合わせを導き出すことができます。 いずれにしても、SQL CONVERT 関数は SQL CAST 関数よりも若干動作が優れています
例 7: CONVERT を使用した計算でのスタイル コード
この例では、Adventureworks2016 データベースの “HumanResources.EmployeeHistory” テーブルからレート フィールドをリストアップします。 SQLの2列目には、データ型をchar(10)、式をrate data multiplyとしたSQL CONVERT関数を使用しています。
1つ目のSQLではスタイルコードを使用していませんが、2つ目と3つ目のSQLではスタイルコードを1に設定しています。
|
1
2
3
4
5
6
7
8
9
|
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000) AS TextPrice
FROM HumanResources.EmployeePayHistory;
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000, 0) AS TextPrice
FROM HumanResources.EmployeePayHistory;
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000, 1) AS TextPrice
FROM HumanResources.EmployeePayHistory;
|
出力を見ると、スタイルコードが1のクエリは、数千の位置にカンマ区切りのテキスト出力を返すことがわかります。 スタイルコードが0のクエリでは、カンマセパレータのないテキスト出力が得られます。
まとめ
これまでに、SQL キャスト、SQL コンバーター、および SQL コンバーターについて説明しました。 SQL cast、SQL try_cast、SQL convertの各関数と、文字列、整数、日時の値をどのように変換するかについて説明しました。 また、スタイルコードを使用した SQL CONVERT 関数についても説明しました。 暗黙的なSQL変換関数と明示的なSQL変換関数の例を見てみました。 また、SQL CAST関数とSQL CONVERT関数の違いを理解しました。 また、SQL ISNULLでは、SQL try_castとともに条件式が使われていますが、これもtryの部分を使わずにやっていたら、値を返さずに失敗していたでしょう。
今回は以上です。この記事を楽しんでいただけましたら幸いです。 お気軽にコメントしてください。
- Author
- Recent Posts
私の専門分野は、& 高可用性ソリューションとクロスプラットフォームのDBマイグレーションの設計です。 現在取り組んでいる技術は、SQL Server、PowerShell、Oracle、MongoDBです。
Prashanth Jayaramの投稿をすべて見る
- SQLでのデータベース監査の概要 – 1月28日, 2021年
- Azure SQL データベースとオンプレミス SQL Server の間で Azure Data Sync をセットアップする方法 – 2021年1月20日
- PowerShell を使用して Azure SQL データベースのインポート/エクスポート操作を実行する方法 – 2021年1月14日