この記事では、SQL ServerでのSQL Coalesce関数を使用した文字列操作について説明します。
文字列操作は、ビジネスがレポートに結果として使用または表示する方法で別の形式の既存データを生成するプロセスです。 以前のSQL文字列関数の記事では、私は組み込みのSQL Server文字列関数を使用してデータ準備とデータ管理タスクについて説明し、データMungingとSQL部分文字列関数の,
SQL serverには、データの操作や変換を可能にする組み込みの文字関数もいくつかあります。 同時に、データセットを調べ、データ値を探索し、必要に応じて値をエンコードまたはデコードして意味のあるデータを生成することが重要です。 データセット内の欠損値をナビゲートする方法、計算、クエリ、レポート、データセットの準備への影響を理解し、Null値が結果セットを台無しにすることを避けるためのテクニックを考え出すことが重要です。
NULL値とは何ですか？,
欠損値を持つデータセットの潜在的な地雷原をナビゲートし、Nullを踏まないようにする方法を掘り下げる前に、まずNULLが何であるかを簡単に見
Wikipediaによって定義されている
Null（またはNULL）は、データ値がデータベースに存在しないことを示すために構造化クエリ言語で使用される特別なマーカーです。 導入されたクリエイター-オブ-ザ-イヤーリレーショナルデータベースモデルでは、E.F., Codd,SQL Nullは、すべての真のリレーショナルデータベース管理システム(RDBMS)が”不足している情報と適用できない情報”の表現をサポートするという要件を満たすのに役立ちます。 Coddはまた、データベース理論においてNullを表すために小文字のギリシャ語omega(ω)記号を使用することを導入した。 SQLでは、NULLはこのマーカーを識別するために使用される予約語です。 …これは0の値と混同しないでください。 Null値は値の欠如を示します—値の欠如は、答えの欠如が”いいえ”の答えと同じものではないのと同じように、ゼロの値と同じものではありません。,
さらに…
SQL nullは状態であり、値ではありません。 この使用法は、参照のnull値がオブジェクトを指していないことを意味するほとんどのプログラミング言語とはかなり異なります。
SQLは、SQLクエリで文字データを操作するための便利な機能を提供します。
SQL Coalesce関数
SQL Coalesce関数とIsNull関数は、NULL値を処理するために使用されます。 式の評価プロセス中に、NULL値はユーザー定義値に置き換えられます。,
SQL Coalesce関数は引数を順番に評価し、常に定義された引数リストから最初のnull以外の値を返します。
構文
COALESCE(expression)
SQL Coalesce関数のプロパティ
- 式は同じデータ型でなければなりません
- 複数の式を含めることができます
- SQL Coalesce関数は、Case式の構文ショートカットです
- アウトプット。,
例:
SQLは文字列連結操作で合体します
次の例では、いくつかの値を連結します。 しかし、繰り返しますが、NULL値がある場合に何が起こるかを知らせるのは単なるレビューです。 それでは、先に進んでT-SQLを実行しましょう。 また、文字列連結操作を処理している間にNULL値が発生したことがわかります。 SQL serverは、NULL値が検出されるたびにNULLを返します。 結果は、firstname、null、およびlast nameの組み合わせではありません。,
|
1
|
SELECT firstName +’ ‘+MiddleName+’ ‘+ LastName FullName FROM Person.Person
|
Let us handle the NULL values using a function called SQL COALESCE. It allows handling the behavior of the NULL value., したがって、この場合、coalesce SQL関数を使用して、ミドルネームのNULL値を値”（Char（13）-space）に置き換えます。 SQLステートメントはまだ三つの名前をすべて連結する必要がありますが、NULL値は出力に表示されません。 これで、null値の場合、フルネームが真ん中にスペースで表示されることがわかりました。 このようにして、列の値を効率的にカスタマイズすることができます。,
|
1
|
personからfirstname+”+coalesce(middlename,”)+”+lastnameを選択します。Person
|
SQL関数とピボットを合体させる
次の例では、テーブル’state’から連結されたnull以外の値を返します。 場合によっては、連結された静的値を変数に割り当てる必要がある場合があります。, この場合、city列の値は、Coalesce SQL関数を使用して解析され、単一引quoteで連結されて値の文字列が準備されます。 その後、出力がさらに操作され、入力値の有効な文字列を取得するために最後の文字が削除されます。,
Output:
|
1
|
SELECT ‘(‘+substring(@col,1,len(@col)-1)+’)’
|
Scalar user-defined function and SQL Coalesce function
A user-defined function is created to return a string specific to the provided input and then the output is grouped using a grouping clause., 次の例では、スカラー値関数は、指定された’City’入力に対して、’,’で区切られた連結された文字列値を返します。 次の例では、state列がグループ化され、そのcities値が区切り文字’,'(カンマ)で連結されて区切られている出力を返します。 SQL Server2017を使用している場合は、STRING_AGGを使用することもできます。 Sql Server2017のトップSQL文字列関数
関数名dboの呼び出し方法は次のとおりです。selectステートメント内のtfn_CoalesceConcat。,
The output is a concatenated stream of values separated by a delimiter ‘,’
|
1
2
3
4
|
SELECT state, city = dbo.tfn_CoalesceConcat(state)
FROM dbo.,state
GROUP BY state
ORDER BY state;
|
SQL Coalesce関数を使用したデータ検証
次の例では、緊急従業員の連絡先を検索します。 通常、どの組織でも、従業員の電話番号は職場、自宅、携帯電話の列にリストされます。
緊急連絡先がリストされていない従業員を見つける方法、つまり緊急連絡先を持つ従業員のすべての詳細を引き出しましょう。,
次の例では、tb_EmergencyContactはすべての従業員の連絡先番号を保持します。
SQL Coalesce関数は、homephone、workphone、およびcellphone列を選択するために使用されます。 NULL値の場合、値’NA'(該当しない)、リテラル文字列が返されます。
SQL Coalesceと計算列
次の例では、SQL COALESCEを使用して、hourlywage列、salary列、およびcommission列の値を比較し、列にあるnull以外の値のみを返します。,
次のT-SQLは、すべての従業員に支払われた合計給与を一覧表示するために使用されます
さて、SQL ServerでSQL Coalesce関数を使用して計算列を作成する例を見てみましょう
一般に、テーブルで式を使用する必要があるかもしれません。 テーブルでは、多くの場合、複数の既存の列を使用して計算され、テーブルのスカラー値が少ない値を計算する必要があります。 また、これらの列は他の列に依存します。, このようにして、Coalesce SQL関数を使用して計算列を作成して、NULL値を効率的に処理できるようにすることができます。
これで、単純なSELECTステートメントが事前に計算された結果を表示することがわかります。
SQL COALESCEおよびCASE式
SQL COALESCE関数は、CASE式を使用して構文的に表すことができます。 たとえば、わかっているように、Coalesce関数は最初のNULL以外の値を返します。,
SELECT COALESCE(expression1,expression2,expression3)FROM TABLENAME;
上記のCoalesce SQLステートメントは、CASEステートメントを使用して書き換えることができます。,id=”6df95ef3dd”>