SQL Server CHARINDEX和PATINDEX詳解

如果你寫過非常多程式，你可能偶爾會碰到要確定字元或字元竄串否包含在一段文字中，在這篇文章中，我將討論使用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中的字串的搜尋、控制、分析非常有協助。

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/Hello_World_wusu/archive/2009/09/08/4533177.aspx