通常,在寫搜尋的功能時,都是使用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來的快速.而且不會有特殊字元的問題(有的話應該也只有單引號...).
參考資料 :
http://media.datadirect.com/download/docs/slnk/devref/scalarfn.html
http://technet.microsoft.com/zh-tw/library/ms173545.aspx
Saving ...