[SQL] 搜尋金額的範圍內容

資料庫真是一項好用的東西，尤其是精通 SQL 語法的話，對於 programmer 來說，真是如虎添翼阿~~

回想起大學時代修資料庫系統的課，老師第一堂課就說：所有的程式，資料的運算如果能用 SQL 解決，就一定要用 SQL 解決，不然，你花大錢買他幹嘛!!如果只是用資料庫來存資料，取資料都只有簡單的 CRUD (Create,Replace,Update and Delete)，那跟直存成檔案有啥差別!!

這句話讓我深深的體會在這次的 CASE 中。因為我這次接的 CASE 是關於撥款的系統，很多資料的運算要處哩，如果全部都讓我寫程式自己作，那效能一定會是最令人詬病的!!

回到我們的主題，光看這主題一定不能了解我要解的問題??

precondition : the database is MS SQL Server 2K

那我們就來描述一下我所遇到的問題：

如果使用者給你的 table 長這樣

Table A

range ....

------------------------------------------

0~100 ...

101~200...

...

但是使用者希望他是輸入 51，然後你就要把第一筆的資料撈給他!!

剛開始我就想，哇!! 好難~~但是，問題就是要解，那要怎樣解勒??

一步步來，首先，我們要先試試看能不能把 0~100 這種字串透過 SQL 來拆解，透過線上的文件中我們可以由 String 資料型態的相關 function 值中發現一個 function - CHARINDEX(pattern, str)

於是我們就寫下

SELECT CHARINDEX('~','0~100')

結果的確是我們要的結果，CHARINDEX function 是用來將特定的 pattern 來給與 str 中的位置，由上面的測試結果為 2 就可以知道這 function 對我們是有用的~~

接著就是要取出部分的字串，這項要求很熟悉，

沒錯要用 SUBSTRING(str,begin,len)，於是我們就用它來先取出左邊的範圍吧!!

DECLARE @x AS VARCHAR

SET @x='0~100'

SELECT SUBSTRING(@x, 1, CHARINDEX('~', @x)-1)

上面的作法是為了不要每次都打 '0~100' ，所以宣告一個變數來儲存~~

上面的 SQL 語法得到的結果會是 0 ，恭喜~~我們成功了左邊的範圍。

那右邊的範圍可以如法炮製嗎?? 好像還差一個東西：我們要如何知道字串的長度勒??

答案是 LEN(str) function

所以我們來整合一下吧~~

DECLARE @x AS VARCHAR

SET @x='0~100'

SELECT SUBSTRING(@x, 1, CHARINDEX('~', @x)-1) AS leftRange,

         SUBSTRING(@x, CHARINDEX('~', @x)+1, LEN(@x)) AS rightRange

完成了一半囉!!

接下來就是要將剛剛的 SQL 語法套用到我們的問題中。

SELECT SUBSTRING(range, 1, CHARINDEX('~', range)-1) AS leftRange,

SUBSTRING(range,CHARINDEX('~',range)+1,LEN(range)) AS rightRange

FROM A

結果就是

leftRange    rightRange

-----------------------------------

0                100

101             200

...

的確這樣我們就可以拆解出範圍囉~~

如果們把這樣的結果當作 subquery，對這樣的結果作進一步的包裝，那我們就可以將使用者的要求達成囉~~ 下面假這使用者輸入的內容為 @input

DECLARE @input AS VARCHAR

SET @input='51'

SELECT T.*

FROM (

  SELECT *, SUBSTRING(range, 1, CHARINDEX('~', range)-1) AS leftRange, SUBSTRING(range,CHARINDEX('~',range)+1,LEN(range)) AS rightRange  FROM A

) AS T WHERE @input BETWEEN T.leftRange AND T.rightRange

問題得到解決了~~

回到一開始，如果這樣的問題要用程式來解決的話，勢必要花掉很多的時間，所以能用 SQL 幫我們運算，何樂而不為呢~~

以上的經驗分享之~~