sql common mistake of incorrect predicate orderConversion failed when converting varchar value 'abc' to data type int

たとえば、次の Employee テーブルの場合。

<テーブル Eno 名前 エコード エディプス 1 アレン abc 売上高 2 最大 102 マーケティング 3 病棟 55 マーク 4 サム xyz 売上高 上記の表で、社員コードがM部門の名前で始まり、100より大きい社員を検索するために、プログラマーは通常次の方法でテーブルに問い合わせをします。

select * from Employee where Edept like 'M%' and CAST(Ecode as Int)>100

しかし、上記のクエリを実行すると、エラーが発生します。

varchar 値 'abc' のデータ型 int への変換に失敗しました。

このクエリが失敗する理由は、まさに先に述べたとおり、述語の実行順序が指定されていないからです。この場合、2番目の述語を直接評価すると、そもそも変換エラーになります。

その代わり、ここではCASE式を使うことで、int型に変換されるのは正規のデータだけであることが保証されます。

select * from  Employee where Edept like 'M%' and CASE WHEN Ecode not like '%[^0-9]%' then CAST(Ecode as Int) END>100