sql server charindex函數和patindex函數詳解)

     charindex和patindex函數常常用來在一段字元中搜尋字元或字串。假如被搜尋的字元中包含有要搜尋的字元，那麼這兩個函數返回一個非零的整數，這個整數是要搜尋的字元在被搜尋的字元中的開始位元。patindex函數支援使用萬用字元來進行搜尋，然而charindex不支援萬用字元。接下來，我們逐個分析這兩個函數。

怎樣使用charindex函數
       charindex函數返回字元或字串在另一個字串中的起始位置。charindex函數調用方法如下：
       charindex ( expression1 , expression2 [ , start_location ] )
       expression1是要到expression2中尋找的字元中，start_location是charindex函數開始在expression2中找expression1的位置。
       charindex函數返回一個整數，返回的整數是要找的字串在被找的字串中的位置。假如charindex沒有找到要找的字串，那麼函數整數“0”。讓我們看看下面的函數命令執行的結果：
      charindex(sql, microsoft sql server)
      這個函數命令將返回在“microsoft sql server”中“sql”的起始位置，在這個例子中，charindex函數將返回“s”在“microsoft sql server”中的位置11。
接下來，我們看這個charindex命令：
      charindex(7.0, microsoft sql server 2000)

      在這個例子中，charindex返回零，因為字串“7.0” 不能在“microsoft sql server”中被找到。

      接下來通過兩個例子來看看怎樣使用charindex函數來解決實際的t-sql問題。

      第一個例子，假設您要顯示northwind資料庫customer表前5行連絡人列的last name。這是前5行資料
           contactname
           ------------------------------ 
           maria anders
           ana trujillo
           antonio moreno
           thomas hardy
           christina berglund
      您能夠看到，customname包含客戶的first name和last name，他們之間被一個空格隔開。我用charindx函數確定兩個名字中間空格的位置。通過這個方法，我們能夠分析contactname列的空格位置，這樣我們能夠只顯示這個列的last name部分。這是顯示northwind的customer表前5行last name的記錄！
      select top 5 substring(contactname,charindex( ,contactname)+1 ,len(contactname)) as [last name] from northwind.dbo.customers
下面是這個命令輸出的結果。
           last name
           ------------------------------ 
           anders
           trujillo
           moreno
           hardy
           berglund
      charindex函數找到first name和last name之間的空格，所以substring函數能夠分開contactname列，這樣就只有last name被選出。我在charindex函數返回的整數上加1，這樣last name不是從空格開始。
      在第二個例子中，即如說您要計算記錄中，某一個欄位包含特定字元的任何記錄數。charindex函數能夠方便的解決您的問題。計算northwind.dbo.customer表中addresses欄位中包含單詞road或他的縮寫rd的記錄數，選擇語句類似這樣：
       select count(*) from northwind.dbo.customers

       where charindex(rd,address) > 0 or charindex(road,address)> 1

 

怎樣使用patindex函數
      patindex函數返回字元或字串在另一個字串或運算式中的起始位置，patindex函數支援搜尋字串中使用萬用字元，這使patindex函數對於變化的搜尋字串很有價值。patindex函數的命令如下：
      patindex ( %pattern% , expression )
      pattern是您要搜尋的字串，expression是被搜尋的字串。一般情況下expression是個表中的一個欄位，pattern的前後需要用“%”標記，除非您搜尋的字串在被收縮的字串的最前面或最後面。
      和charindex函數相同，patindex函數返回搜尋字串在被搜尋字串中的起始位置。假如有這樣一個patindex函數：
      patindex(%bc%,abcd)
      這個patindex函數返回的結果是2，這和charindex函數相同。這裡的%標記告訴patindex函數去找字串“bc”，不管被搜尋的字串中在“bc”的前後有多少字元！
      假如您想知道被搜尋字串是否由特定的字串開始，您能夠省去前面的%標記。patinded函數就要這樣寫：
      patindex(ab%,abcd)
      這個命令執行的結果返回1，表示搜尋的字串“ab”在被搜尋的字串中“abcd”被找到。
      使用萬用字元能夠編輯比我以上舉得簡單例子複雜得多的搜尋字串。假如說您要確定一個字串是否包含字母a和z，更有任何數字，這個parindex函數命令可能像這樣：
      patindex(%[a,z,0-9]%[a,z,0-9]%[a,z,0-9]%,xyzabc123)
      注意在上面這個例子中的搜尋字元部分使用了很多的通陪符。察看sql server聯機叢書能夠獲得更多關於通佩符的資訊。接下來，我們用兩個例子來看patindex和select怎麼聯合起來使用。
      假設您想要找出northwind.dbo.categories表中description欄位中是包含單詞“bread”或“bread”的任何記錄，那麼選擇語句就可能是這樣：
        select description from northwind.dbo.categories
        where patindex(%[b,b]read%,description) > 0
      這裡我用萬用字元來確定大寫和小寫“b”。我在notthwind資料庫中執行這個指令碼後，得到下面的結果：
           description
           --------------------------------------------------------
           desserts, candies, and sweet breads
           breads, crackers, pasta, and cereal
      這是再用另外一個額外的萬用字元來尋找一些記錄的例子。這個例子是怎樣選出上面的查詢結果中，description欄位的第二子字母不是“e”的紀錄。
                   select description from northwind.dbo.categories     
                   where patindex(%[b,b]read%,description) > 0  
                   and patindex(_[^e]%,description) = 1        
      通過在條件陳述式中增加一個使用^萬用字元的patindex函數，我們能夠過濾掉“dessert, candies, and sweet breads”這條記錄。上面的查詢結果只有一條記錄。
           description
           --------------------------------------------------------
           breads, crackers, pasta, and cereal 
總結
      您現在能夠發現charindex和patindex搜尋字串時的區分了吧。patindex函數支援使用萬用字元，能夠用在很多有變化的尋找中。而charindex不能夠。根據您自己不同的情況，這兩個函數對您在sql server中的字串的搜尋、控制、分析很有協助。