WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

SQL Server の数値型を LIKE 句を使ってあいまい検索

by WebSurfer 19. June 2021 12:55

SQL Server の数値型は、文字列型と同様に、そのまま直接 LIKE 句を使ってあいまい検索ができるようです。下の画像を見てください。UnitPrice 列は money 型ですが、[UnitPrice] LIKE '%2%' という条件が有効になっています。

SSMS での検索結果

知ってました? 実は何を隠そう自分は最近まで知らなかったです。(汗) 数字型はまず文字列型に変換してから、それに LIKE 句を使うものだと思ってました。

調べてみると、Microsoft のドキュメント「LIKE (Transact-SQL)」に、

"引数が文字列データ型でない場合、SQL Server データベース エンジン は可能であれば引数を文字列データ型に変換します。 If any one of the arguments isn't of character string data type, the SQL Server Database Engine converts it to character string data type, if it's possible."

・・・と書いてあります。実際に試してみると、上の画像のように money 型の UnitPrice 列も LIKE 句を使ってあいまい検索ができました。

Microsoft のドキュメントが言う「可能であれば」がどこまでの範囲か調べ切れていませんが、自分が SQL Server 2012 で試した限りでは int 型と money 型は可能な範囲に入るようです。

ADO.NET + SqlClient を使った .NET Framework のアプリケーションでも同じことができます。パラメータ化も可能です。ただし、パラメータ化する場合は、パラメータは文字列型として扱う必要がありますが。

上の画像と同様な LIKE 句を使って検索を行う .NET Framework コンソールアプリのサンプルコードを以下に載せておきます。ADO.NET + SqlClient を使い、SQL 文はパラメータ化しています。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleAppLIKE
{
    public class Product
    {
        public int ProductID { get; set; }

        public string ProductName { set; get; }

        // UnitPrice 列は NULL 可なので Nullable とした
        public decimal? UnitPrice { set; get; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string connString = "接続文字列";
            string selectQuery =
                "SELECT [ProductID] ,[ProductName] ,[UnitPrice] FROM [Products] " +
                "WHERE [ProductName] LIKE N'%' + @ProductName + N'%' AND " +
                "[UnitPrice] LIKE N'%' + @UnitPrice + N'%'";

            string productName = "ch";
            string unitPrice = "2";            
            List<Product> productList = new List<Product>();

            using (var connection = new SqlConnection(connString))
            {
                using (var command = new SqlCommand(selectQuery, connection))
                {
                    var p1 = new SqlParameter("@ProductName", SqlDbType.NVarChar);
                    p1.Value = productName;

                    // UnitPrice 列は money 型だが LIKE 句を使ってあいまい検索
                    // する場合はパラメータの型は文字列とする
                    var p2 = new SqlParameter("@UnitPrice", SqlDbType.NVarChar);
                    p2.Value = unitPrice;

                    command.Parameters.Add(p1);
                    command.Parameters.Add(p2);

                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var product = new Product
                            {
                                ProductID = reader.GetInt32(0),
                                ProductName = reader.GetString(1),

                                // UnitPrice 列は NULL 可なのでその対応
                                UnitPrice = reader.IsDBNull(2) ?
                                            null : (decimal?)reader.GetDecimal(2)
                            };

                            productList.Add(product);
                        }
                    }
                }
            }

            foreach (Product p in productList)
            {
                Console.WriteLine($"PriductID: {p.ProductID}, " +
                    $"ProductName: {p.ProductName}, UnitPrice: {p.UnitPrice}");
            }
        }
    }
}

サンプルコード中のコメントにも書きましたが、UnitPrice 列は money 型ですが LIKE 句を使ってあいまい検索する場合はパラメータの型は文字列とする必要がありますので注意してください (例えば、SqlDbType.NVarChar を SqlDbType.Deciaml にするとエラーになります)。

上のコードの実行結果は以下の通りで、上の画像の SSMS での実行結果と同じになっています。

ADO.NET での検索結果

Tags: , , ,

SQL Server

About this blog

2010年5月にこのブログを立ち上げました。その後 ブログ2 を追加し、ここは ASP.NET 関係のトピックス、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  September 2021  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar