SQL語法like的一些問題

2008-04-01

SQL語法like的一些問題

通常,在寫搜尋的功能時,都是使用like的方式來寫,
而寫法通常是像

select * from tableName where columnName like '%xxxx%'

而最近一個案子在驗收的時候,
驗收的人員在測試時輸入"%"(百分比符號),
結果列出了所有的資料.
然後說這有個bug...輸入"%"應該只出現資料中有%符號的資料.
為什麼會出現所有的資料即使沒有%符號.

其實想想也對,當使用者入一些SQL的特定字元時,
他可能只是想要搜尋字串中真的有這個字元的資料,
而不是想要做模糊比對.
所以客戶有這樣的要求,那我們就這樣改...

不過,之前並沒有留意過要怎麼在SQL語法中可以搜尋特定字元,
只知道在避免SQL Injection時,是使用兩個單引號取代一個單引號.
但是對於%或是_(底線)的符號,就沒留意過了.
而在經過大鈞(MS MVP)的協助,找到一些資料後發現有兩種方法可以用.

比較易懂的方法 - 使用中括弧包住特殊字元.

select * from tableName where columnName like '%[%]%'

另一個是使用escape

select * from tableName where columnName like '%\%%' escape '\'
或寫成
select * from tableName where columnName like '%\%%' {escape '\'}

escape後的'\'可以是其他的字元,像是'a'或是'@'都可以.只要配合like後的字串即可.

這兩個的結果都是會搜尋出資料中任何位置有%字元的資料.

但是,除了用like外,還有一個更方便且快速的語法,
因為客戶只需要輸入甚麼就是甚麼.所以他不會需要像是輸入"中_航空"就找出有"中國航空"與"中華航空"的資料,
所以直接使用下面的語法就可以解決這問題,而且搜尋的速度也更加快速了

select * from tableName where charindex('%',fieldName) > 0

charindex會傳回字串在資料欄位中的位置,所以當欄位內容有資料時,傳回的資料就會大於0,
用此來取得搜尋的資料會比用like來的快速(註一).而且不會有特殊字元的問題(有的話應該也只有單引號...).

註一 : 會比較快速的前提是該欄位沒有建立索引的狀況下,如果有建立索引,charindex就不見得比較快.