萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 模糊查詢完全指南:LIKE 萬用字元、特殊字元跳脫、Access 差異與效能

LIKE 基本語法


SELECT 欄位 FROM 資料表 WHERE 欄位 LIKE '模式';

四大萬用字元詳細說明

符號意義範例模式符合範例
%零或多個任意字元'Mc%'McBadden, McDonald
_單一任意字元'_heryl'Cheryl, Sheryl (六字元)
[...]括號內任一字元'[CK]ars[eo]n'Carsen, Karsen, Carson, Karson
[^...]非括號內字元'M[^c]%'MacFeather (第二字元非 c)

實戰範例集


-- 1. 以某字串開頭
SELECT * FROM zipcode WHERE address LIKE '123%';

-- 2. 以某字串結尾
SELECT * FROM zipcode WHERE city LIKE '%Taipei';

-- 3. 包含關鍵字 (最常用)
SELECT * FROM zipcode 
WHERE address LIKE '%' + @keyword + '%' 
   OR city LIKE '%' + @keyword + '%' 
   OR province LIKE '%' + @keyword + '%'
ORDER BY province, city, address;

-- 4. 固定長度模式
SELECT * FROM user WHERE username LIKE '_ _ _'; -- 正好三個字元

-- 5. 字元集合
SELECT * FROM user WHERE name LIKE '[張李王]%'; -- 姓張、李、王

-- 6. 排除字元集合
SELECT * FROM user WHERE name LIKE '[^張李王]%'; -- 非張李王姓

關鍵邏輯陷阱:AND vs 連續 LIKE


-- 錯誤:只能找「三」在「貓」之前的
SELECT * FROM [user] WHERE u_name LIKE '%三%貓%';

-- 正確:同時包含「三」與「貓」,順序不拘
SELECT * FROM [user] 
WHERE u_name LIKE '%三%' AND u_name LIKE '%貓%';

特殊字元跳脫 (查詢內容含 %, _, [ )

將萬用字元置於 [] 內視為字面值:


-- 查詢含有 '%' 字元的列
SELECT * FROM tbl WHERE col LIKE '%[%]%';

-- 查詢含有 '_' 字元
SELECT * FROM tbl WHERE col LIKE '%[_]%';

-- 查詢含有 '[' 字元 (必須最先處理)
SELECT * FROM tbl WHERE col LIKE '%[[]%';

通用跳脫函數 (VBScript/ASP 經典)


Function SQLEncode(str)
    str = Replace(str, "[", "[[]")   ' 必須最先處理
    str = Replace(str, "_", "[_]")
    str = Replace(str, "%", "[%]")
    SQLEncode = str
End Function

Access vs SQL Server 萬用字元差異 (重要!)

功能Access (ACE/Jet)SQL Server (T-SQL)
零或多字元*%
單一字元?_
字元集合[...][...]
排除集合[!...][^...]

-- Access 查詢視窗
SELECT * FROM a WHERE name LIKE '*b*';

-- SQL Server / 程式碼中 (ADO.NET, JDBC 等)
SELECT * FROM a WHERE name LIKE '%b%';

警告:在 Access 設計師測試用 *,但程式碼 (C#, Java, Python) 傳送給 SQL Server 時必須用 %。混用會導致查無資料或語法錯誤。

預存程序中動態模糊查詢


CREATE PROC usp_SearchQuestions
    @Keyword NVARCHAR(100),
    @IsFinish BIT
AS
SELECT * FROM Questions 
WHERE QTitle LIKE N'%[' + @Keyword + ']%'  -- 書寫格式:成對方括號包裹關鍵字
  AND IsFinish = @IsFinish;

效能考量:避免前置萬用字元

  • LIKE '關鍵字%':可使用索引 (Index Seek)。
  • LIKE '%關鍵字%':無法使用索引,必須掃描全表/索引 (Index Scan)。

大量資料模糊查詢優化方案

  1. 全文檢索:CONTAINS, FREETEXT (需建立全文索引)。
  2. 計算欄位 + 索引:反轉字串 REVERSE(col) 建立索引,查詢尾碼時轉為前置。
  3. 外部搜尋引擎:Elasticsearch, Solr, Azure Cognitive Search。
  4. N-gram / Trigram 索引:PostgreSQL pg_trgm 擴充套件等。

總結

掌握 LIKE 四大萬用字元、[] 跳脫技巧、Access 差異與前置萬用字元效能陷阱,是撰寫正確且高效模糊查詢的關鍵。大量資料場景請轉向全文檢索或專用搜尋引擎。

copyright © 萬盛學電腦網 all rights reserved