sql常用函式詳解（一）——字串擷取

1.substring函式——擷取字串

  SUBSTRING ( expression, start, length )

    expression——字串、二進位制字串、文字、影像、列或包含列的表示式。請勿使用包含聚合函式的表示式。

    start——整數或可以隱式轉換為int 的表示式，指定子字串的開始位置。

    length——整數或可以隱式轉換為 int 的表示式，指定子字串的長度

    left(field,length) ——函式返回field最左邊的length個長度的字串

    right(field,length)——函式返回field最右邊的length個長度的字串

eg1:取定長定位置的字串

select substring('abdcsef',1,3)

result:  abd

 

2.CHARINDEX函式——返回字元或者字串在另一個字串中的起始位置

CHARINDEX ( expression1 , expression2 [ , start_location ] ) ——  CHARINDEX函式返回一個整數，返回的整數是要找的字串在被找的字串中的位置。Expression1是要到expression2中尋找的字元中，start_location是CHARINDEX函式開始在expression2中找expression1的位置。  

CHARINDEX('SQL', 'Microsoft SQL Server')

這個函式命令將返回在“Microsoft SQL Server”中“SQL”的起始位置，在這個例子中，CHARINDEX函式將返回“S”在“Microsoft SQL Server”中的位置11。

eg1:取不定長不同位置的字串

  

       我們只想要得到'roomno'中的房間號，發現起始字元位置並不是固定的，而且，我們需要的房間號長度也不固定。

此時，我們可以運用‘charindex’這個函式就可以輕鬆搞定，它是用來定位某個特定字元在該字串中的位置，即該函式

得到的結果是一個用來表示某個特定字元位置的數字。執行如下程式碼：

select 
room_stand=substring(roomno,charindex('元',roomno)+1,charindex('室',roomno)-charindex('元',roomno)-1) 
from PROPERTY_room
where roomno like '%單元%室%' 

    result:

3.STUFF函式——刪除指定長度的字元，並在指定的起點處插入另一組字元。

STUFF ( character_expression , start , length ,character_expression )

SELECT STUFF('abcde', 2, 3, '123')

   result： a123e

4.patindex函式——返回pattern字串在表示式expression裡第一次出現的位置，起始值從1開始算。

1. PATINDEX ( '%pattern%' , expression )——'%pattern%'的用法類似於 like '%pattern%'的用法，也就是模糊查詢其pattern字串是否是expression找到，找到並返回其第一次出現的位置。

eg1：

select patindex('%abb%','abcaabbeeabb')

結果5，也就是abb第一次出現的位置。

2.PATINDEX ( '%pattern' , expression )——'%pattern' 類似於 like '%pattern'的用法，前面用模糊查詢，也就是查詢pattern的結束所在expression的位置，也就是從後面匹配起查詢。

eg2：

select patindex('%abb','abcaabbeeabb')

返回10，也就是abb在後面第一次出現的位置。

select patindex('%abb','abcaabbeeabbr')

返回0，後面的第一個字母r和abb不匹配，所以返回0

3.PATINDEX ( 'pattern%' , expression )——這就相當於精確匹配查詢，也就是pattern,expression完全相等。

eg3：

select patindex('abb%','abbcaabbeeabb')

返回1，也就相當於起始值

select patindex('abb%','arbbcaabbeeabb')

返回0，開頭找不到就返回0,後面無論有多少都不管。

4.PATINDEX ( 'pattern' , expression )——這就相當於精確匹配查詢，也就是pattern,expression完全相等。

select patindex('abb','abb')

返回1,完全相等

select patindex('abb','abbr')

返回0,不完全相等

5. patindex('%[pattern]%','ddabcaabbeeabbr')，

   patindex('%[^pattern]%','ddabcaabbeeabbr')

在此先說[]的用法，[]是指定某些特殊的字元。[^]除[]之外的字串,[]這其中的每一個

如：

Symbol Meaning

LIKE '5[%]' 5%

LIKE '[_]n' _n

LIKE '[a-cdf]' a, b, c, d, or f

LIKE '[-acdf]' -, a, c, d, or f

LIKE '[ [ ]' [

LIKE ']' ]

LIKE 'abc[_]d%' abc_d and abc_de

LIKE 'abc[def]' abcd, abce, and abcf

如：

select patindex('%[d]%','rcrdsddddaadadffdr')

返回4，[]中d在字串rcrdsddddaadadffdr的第一次出現的位置。

select patindex('%[cd]%','rcrdsdddrdaadadffdr')

返回2，[]中c,d在其中一個的位置，返回最先出現的這個位置，c在此字串裡第一次出現位置是2，而d是4，結果取最先的那個。

select patindex('%[sd]%','rcrdsdddrdaadadffdr')

返回4，[]中c,d在其中一個的位置，返回最先出現的這個位置，s在此字串裡第一次出現位置是5，而d是4，結果取最先的那個。

select patindex('%[^r]%','rrrdsdddrdaadadffdr')

返回4，除[]中的字串的匹配字元，第一次出現d不在[^r]裡，所以就找到第一次這位。

select patindex('%[^rd]%','rrrdsdddrdaadadffdr')

返回5，除[]中的字串的匹配字元，第一次出現s不在[^rd]裡，所以就找到第一次這位。

select patindex('%[^rsd]%','rrrdsdddrdaadadffdr')

返回11，除[]中的字串的匹配字元，第一次出現a不在[^rsd]裡，所以就找到第一次這位。

 

 

 

  eg：去除括號及括號內內容（批量化處理）

--> 測試資料：#
if object_id('tempdb.dbo.#') is not null drop table #新建臨時表
create table #(col varchar(46))
insert into #
select '重慶唯遠實業有限公司(2009年01月05日)' union all
select '江蘇蘇美達船舶工程有限公司(2009年01月05日)' union all
select '上海啟門機電有限公司(2009年01月04日)' union all
select '小洋人生物乳業集團有限公司(2009年01月04日)' union all
select '瀋陽鼎冷機電裝置有限公司(2009年01月04日)' union all
select '嘉柏(中國)國際貨運代理有限公司(2008年12月31日)' union all
select '廣州寶潔有限公司(2008年12月31日)' union all
select '煙臺華科食品有限公司(2008年12月31日)' union all
select '艾來得機械(上海)有限公司(2008年12月31日)' union all
select '上海曉舟船舶配件有限公司(2008年12月31日)' union all
select '上海力弘包裝器材有限公司(2008年12月31日)'


/*
------------------------------
重慶唯遠實業有限公司
江蘇蘇美達船舶工程有限公司
上海啟門機電有限公司
小洋人生物乳業集團有限公司
瀋陽鼎冷機電裝置有限公司
嘉柏(中國)國際貨運代理有限公司
廣州寶潔有限公司
煙臺華科食品有限公司
艾來得機械(上海)有限公司
上海曉舟船舶配件有限公司
上海力弘包裝器材有限公司
*/

解決方案一：

select reverse(stuff(reverse(col), 1, charindex('(', reverse(col)), '')) from #

解決方案二：

update tb
set col = reverse(substring(reverse(col) ,charindex('(',reverse(col)) + 1 , len(col)))
select * from tb

解決方案三：

select 
left(name,patindex('%([0-9][0-9][0-9][0-9]年%',name)-1) as name from [tb]

解決方案四：

select left(col,len(col) - 13) from tb