TSQL 字符串函数:截断和查找

字符串截断函数是指:Stuff 和 SubString,字符串查找函数是:CharIndex 和 PatIndex

一,SubString 截取子串

最常用的字符串函数,用于截取特定长度的子串。

SUBSTRING ( expression ,start , length )

参数说明:

  • start 参数:整数,表示开始位置;字符的序号(index)从1开始,即第一个字符的序号是1;
  • length参数:整数,表示截取字符的最大数量;如果start+Length 大于字符串的总长度,那么返回从Start开始的所有字符;
  • 返回data type:如果expression是char或varchar,那么返回varchar;如果expression是nchar或nvarchar,那么返回nvarchar;

例如:字符串 abcdef,截取从第二个字符开始,共2个字符的子串是:bc

select substring('abcdef',2,2)

二,Stuff 删除字符串的指定部分,并插入相应的字符,即将字符串中指定部分替换为新的字符串

Stuff函数从字符串指定的开始位置,删除指定长度的字符串,并从该位置插入新的字符串。

It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

STUFF ( character_expression , start , length , replaceWith_expression )

参数说明:

  • start:整数,表示删除和插入的开始位置;如果start是0,或大于字符串的总长度,那么该函数返回NULL;
  • length:整数,表示删除字符的最大数量;如果Length+Start大于字符串的总长度,表示删除从Start开始的所有字符;
  • replaceWith_expression :字符类型,表示从开始位置(start)插入的字符串;

例如:从不同位置截断字符串abcdef,查看返回结果

TSQL 字符串函数:截断和查找
declare @str varchar(6)
set @str='abcdef'

select  stuff(@str,7,1,''),
        stuff(@str,0,1,''),
        stuff(@str,3,7,''),
        stuff(@str,3,7,'12345')
TSQL 字符串函数:截断和查找

TSQL 字符串函数:截断和查找

使用stuff函数,必须注意两点:

  1. 如果Length+Start大于字符串的总长度,删除从Start开始的所有字符;
  2. 如果Start是0,或大于字符串的总长度,返回Null;

三,CharIndex 从字符串中查找字符

从字符串search中查找另一个字符串find,如果find存在于search中,返回find在search中第一次匹配的开始位置;如果find不存在于search中,返回0;

CHARINDEX ( find ,search [ , start ] )

参数说明:

  • 在字符串search中查找字符串find,查找的开始位置由参数start确定;
  • 如果start参数没有指定,默认从字符串search的第一个字符开始查找;
  • 如果find 或 search 是null,返回null;
  • 返回值是整数:如果从search中查找到find,那么返回第一次匹配的开始位置;如果查找不到,返回0;

例如:从字符串abcbcdef的不同位置,查找不同的字符

select charindex('bc','abcbcdef'),
        charindex('bc','abcbcdef',3),
        charindex('bce','abcbcdef')

TSQL 字符串函数:截断和查找

结果分析:

  • bc 在 abcbcdef 中出现多次,从第1个字符开始查找,该函数只返回第一次匹配的开始位置;
  • bc 在 abcbcdef 中出现多次,从第3个字符开始查找,该函数只返回第一次匹配的开始位置;
  • bce 不存在 abcbcdef 中,该函数返回0;

四,PatIndex 从字符串中按照Pattern查找特定字符

PatIndex 从字符串中查找pattern,返回第一次匹配成功的开始位置;如果匹配失败,返回0;

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found.

PATINDEX ( '%pattern%' , expression )

pattern:样式字符,能够使用通配符(%,_,[],^),必须以通配符%开始和结尾

示例:从字符串abcbcdef的匹配不同的pattern

select patindex('%bc%','abcbcdef'),
        patindex('%b_b%','abcbcdef'),
        patindex('%b[^c]b%','abcbcdef'),
        patindex('%bce%','abcbcdef')

TSQL 字符串函数:截断和查找

结果分析:

  • Pattern: %bc%  表示bc字符前面和后面有0或多个字符
  • Pattern: %b_b%  表示b和b之间有一个字符,其前面和后面有0或多个字符
  • Pattern: %b[^c]b%  表示b和b之间有一个字符,该字符不能是c,其前面和后面有0或多个字符

五,在使用STUFF 函数时,注意,Start 参数不能是0 或大于字符串的总长度

使用转换函数Convert(varchar(50), Datetime, 127),将日期/时间类型转换成字符串类型,保留的毫秒位数是不固定的:当毫秒不为0时,显式3位毫秒;当毫秒为0时,不显示毫秒。Convert(varchar(50), Datetime, 127) 返回的字符串的格式是 yyyy-mm-ddThh:mi:ss.[mmm]  ,注意:如果毫秒是0,毫秒不显式。

When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.

楼主遇到一个问题,是127和stuff函数一起使用时产生的

1,创建示例数据

TSQL 字符串函数:截断和查找
declare @dt1 datetime
declare @dt2 datetime

set @dt1='2015-01-02 01:23:45.678'
set @dt2='2015-01-02 01:23:45.000'

select CONVERT(VARCHAR(50),@dt1, 127),len(CONVERT(VARCHAR(50),@dt1, 127)),
        CONVERT(VARCHAR(50),@dt2, 127),len(CONVERT(VARCHAR(50),@dt2, 127))
TSQL 字符串函数:截断和查找

TSQL 字符串函数:截断和查找

2,如果 stuff( convert(varchar(50),@datetime,127),20,4) 会出现一个“意外”的结果

TSQL 字符串函数:截断和查找
declare @dt1 datetime
declare @dt2 datetime

set @dt1='2015-01-02 01:23:45.678'
set @dt2='2015-01-02 01:23:45.000'

select CONVERT(VARCHAR(50),@dt1, 127),len(CONVERT(VARCHAR(50),@dt1, 127)),
        CONVERT(VARCHAR(50),@dt2, 127),len(CONVERT(VARCHAR(50),@dt2, 127)),
        STUFF(CONVERT(VARCHAR(50),min(@dt1), 127),20,4,'') ,
        STUFF(CONVERT(VARCHAR(50),min(@dt2), 127),20,4,'')
TSQL 字符串函数:截断和查找

TSQL 字符串函数:截断和查找

原因是:如果 start position 比字符串的长度大,stuff 返回NULL。

STUFF ( character_expression , start , length , replaceWith_expression )

在使用Stuff函数,必须注意:

  • If the starting position is larger than length of the first string, a null string is returned.
  • If the start position is 0, a null value is returned.

六,其他字符串函数

函数ASCII(character_expression) 和 UNICODE(character_expression) 用于获取第一个字符的编码,根据整数编码,通过函数CHAR(code)和NCHAR(code),转换为相应的字符。

函数REPLACE ( string_expression , string_pattern , string_replacement )  用于替换字符

在项目中,有时会遇到清空特定字符的情况,回车、换行、空格的ASCII码值

  • 回车,ASCII码13
  • 换行,ASCII码10
  • 空格,ASCII码32

例如,示例字符返回的ASCII是32,是空格的ASCII编码,但是使用函数LTRIM和RTRIM,怎么都清不掉

select ascii(left(' http://apievangelist.com ',1))

你看到的空格,有可能是换行,其ASCII编码是10,通过REPLACE函数替换“空格”:

replace(' http://apievangelist.com ',char(10),'')

 

参考文档:

PATINDEX (Transact-SQL)
CHARINDEX (Transact-SQL)
SUBSTRING (Transact-SQL)
STUFF (Transact-SQL)

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
分类: SQL Server






本文转自悦光阴博客园博客,原文链接:http://www.cnblogs.com/ljhdo/p/4873740.html,如需转载请自行联系原作者
上一篇:C语言中返回字符串函数的四种实现方法


下一篇:PHP中常用的18个字符串函数