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