突出重围：Transact-SQL管理与开发实例精粹
上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
上一章目录下一章

1.8 常用字符串函数

实践中在开发Transact-SQL代码时，经常会需要对字符串进行各种各样的操作，下面介绍一些常用的字符串函数。

1.8.1 获取字符的ASCII码ASCII

ASCII码是对字符的标准编码。要获取字符的ASCII码就可以通过调用ASCII函数来实现。

1．语法结构

        ASCII (expression)

这里的expression是一个返回char或varchar数据类型的表达式，ASCII函数仅对表达式最左侧的字符返回ASCII码值。

2．返回值

Int数据类型。

3．实例

下面的代码定义一个字符串，最大长度为100。调用ASCII码函数返回该字符串中每个字符的ASCII码值。读者也可以修改其长度和内容。

        SET NOCOUNT ON
        -- 定义获取ASCII码起始位置临时变量
        DECLARE @Beginposition int
        -- 定义字符串变量，存储要获取ASCII码的字符串
        DECLARE @TempString varchar(100)
        -- 初始化变量
        SET @Beginposition = 1
        SET @TempString = ' dancewithwave@163.com'
        -- 对字符串中的每个字符循环执行ASCII函数
        WHILE @Beginposition <= DATALENGTH(@TempString)
          BEGIN
          SELECT ASCII(SUBSTRING(@TempString, @Beginposition, 1)),
              CHAR(ASCII(SUBSTRING(@TempString, @Beginposition, 1)))
            SET @Beginposition = @Beginposition + 1
          END
        SET NOCOUNT OFF
        GO

源代码文件：\代码\0137.sql。

1.8.2 获取ASCII码对应的字符CHAR

如果是int数据类型的ASCII码，可以通过CHAR函数获得该ASCII码对应的字符。

1．语法结构

        CHAR (integer_expression)

这里的interger_expression是返回0～255之间的整数表达式。

2．返回值

CHAR类型字符。

3．实例

下面的代码定义一个字符串，提取字符串中每个字符的ASCII码，并在ASCII码基础上还原字符。

        DECLARE @BeginPosition int
        DECLARE @TempString varchar(100)
        SET @BeginPosition = 1
        SET @TempString = ' dancewithwave@163.com'
        WHILE @BeginPosition <= DATALENGTH(@TempString)
          BEGIN
          SELECT ASCII(SUBSTRING(@TempString, @BeginPosition, 1)) as ' ASCII码’,
              CHAR(ASCII(SUBSTRING(@TempString, @BeginPosition, 1))) as ’字符’
          SET @BeginPosition = @BeginPosition + 1
          END
        GO

源代码文件：\代码\0138.sql。

1.8.3 获取字符的Unicode编码UNICODE

Unicode（统一字符编码标准）主要用于支持非英语语种的字符型数据，采用两个字节来存储一个字符，比如NCHAR就是Unicode字符类型，而CHAR就是普通字符类型。nchar、nvarchar、ntext为Unicode字符型数据类型。

在定义和使用Unicode字符型数据时，需要加入前导标识符N，当向表中录入Unicode字符时并不会存储该标识符。

Unicode函数可以获得字符的Unicode编码。

1．语法结构

        UNICODE ( ' ncharacter_expression' )

这里的ncharacter_expression是nchar或nvarchar类型的表达式。函数仅会返回第一个字符的Unicode编码。

2．返回值

Int数据类型。

3．实例

下面的代码定义一个字符串，最大长度为100。调用Unicode函数返回该字符串中每个字符的Unicode编码值。读者也可以修改其长度和内容。

        DECLARE @BeginPosition int
        DECLARE @nTempString nvarchar(100)
        SET @BeginPosition = 1
        SET @nTempString = N' dancewithwave@163.com'
        WHILE @BeginPosition <= DATALENGTH(@nTempString)
          BEGIN
          SELECT @BeginPosition as ’序号’,
              CONVERT(char(17), SUBSTRING(@nTempString, @BeginPosition, 1)) as ’字符’,
              UNICODE(SUBSTRING(@nTempString, @BeginPosition, 1)) as ' Unicode编码’
          SELECT @BeginPosition = @BeginPosition + 1
          END

源代码文件：\代码\0139.sql。

1.8.4 获取Unicode编码对应的字符NCHAR

NCHAR函数根据Unicode标准的定义，返回具有指定的整数代码的Unicode字符。

1．语法结构

        NCHAR (integer_expression )

这里的integer_expression是介于0～65535之间的正整数，超出该值将返回错误。

2．返回值

Unicode字符。

3．实例

下面的代码通过两种方法来获得Unicode编码的NCHAR字符。一种是NCHAR函数，一种使用CONVERT函数强制进行转换，可以看出两种方法的结果是完全一样的。

        DECLARE @BeginPosition int
        DECLARE @nTempString nvarchar(100)
        SET @BeginPosition = 1
        SET @nTempString = N' dancewithwave@163.com'
        WHILE @BeginPosition <= DATALENGTH(@nTempString)
          BEGIN
          SELECT @BeginPosition as ’序号’,
              NCHAR(UNICODE(SUBSTRING(@nTempString, @BeginPosition, 1))) as ' NCHAR
    函数获得的字符’,
              CONVERT(NCHAR(17), SUBSTRING(@nTempString, @BeginPosition, 1)) as
    'CONVERT函数强制转换获得的字符’,
              UNICODE(SUBSTRING(@nTempString, @BeginPosition, 1)) as ' Unicode编码’
          SELECT @BeginPosition = @BeginPosition + 1
          END
        GO

源代码文件：\代码\0140.sql。

1.8.5 获取字符串第一次出现位置PATINDEX

使用过Word的搜索功能的读者对其快速搜索功能一定很有印象。输入要搜索的字符串，很快就能够在文档中定位到该字符串，并返回其位置信息。

PATINDEX函数就是这样的功能函数，它返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。

1．语法结构

        PATINDEX ( ' %pattern%' , expression )

语法中的主要参数说明如下。

● Pattern：一个文字字符串。可以使用通配符，但pattern之前和之后必须有%字符（搜索第一个或最后一个字符时除外）。因为在Transact-SQL中，%代表模式匹配。

● Expression：一个字符串或字符串数据类型的数据列。

2．返回值

如果expression的数据类型为varchar(max)或nvarchar(max)，则为bigint，否则为int，即pattern指定的字符串在expression中第一次出现的位置。

3．实例

AdventureWorks数据库中的Production架构有一个表Document，存储的是产品维护信息。

● DocumentID字段：int数据类型，not null，为文档主键。

● DocumentSummary:nvarchar(max)，可以为null，为文档概要信息。

下面的代码将查询DocumentID为3的产品维护文档，在概要信息中搜索“ensure”字符串第一次出现的位置。

        USE AdventureWorks;
        GO
        SELECT PATINDEX(' %ensure%' , D.DocumentSummary)
        FROM Production.Document D
        WHERE D.DocumentID = 3;
        GO

源代码文件：\代码\0141.sql。

1.8.6 生成空格字符串SPACE

SPACE函数可以生成任意多个空格组成的字符串。

1．语法结构

        SPACE ( integer_expression )

这里的integer_expression为指示空格个数的正整数。如果要在Unicode数据中包含空格或超过8000个以上的空格，需要使用REPLICATE函数。

2．返回值

Char数据类型的字符。

3．实例

AdventureWorks数据库中的Person架构有一个表Contact，存储的是客户、雇员和供应商的姓名等信息。

● FirstName字段：nvarchar(50)数据类型，not null，联系人姓名。

● LastName:nvarchar(50), not null，联系人的姓氏。

下面的代码将查询表中的联系人姓名和姓氏，对姓氏去除右边的空格，对姓名去除左边的空格，结果通过产生的两个空格字符连接起来。

        USE AdventureWorks;
        GO
        SELECT C.LastName, C.FirstName, RTRIM(LastName) + ' , ' + SPACE(2) +  LTRIM
    (FirstName)
        FROM Person.Contact C
        ORDER BY C.LastName, C.FirstName;
        GO

源代码文件：\代码\0142.sql。

1.8.7 按指定次数重复生成字符串REPLICATE

REPLICATE函数可以按照指定的整数次数，重复生成一个字符串形式的表达式，结果为字符串。

1．语法结构

        REPLICATE ( character_expression , integer_expression )

语法中主要参数说明如下。

● character_expression：字符数据的字母数字表达式，或者可隐式转换为varchar的数据类型的字母数字表达式。

● Integer_expression：一个正整数。如果integer_expression为负，则会返回错误。integer_expression可以是bigint类型。

提示：如果character_expression不是varchar(max)或nvarchar(max), REPLICATE函数将截断长度为8000字节。如果结果超出8000字节，要使用CAST或CONVERT函数将character_expression强制进行转换为大值数据类型。

2．返回值

与character_expression相同数据类型的字符串。

3．实例

下面的代码将复制FirstName字段的值10次后生成结果字符串。

        USE AdventureWorks;
        GO
        SELECT REPLICATE(C.FirstName, 10)
        FROM Person.Contact C
        ORDER BY C.FirstName;
        GO

源代码文件：\代码\0143.sql。

1.8.8 截取子串SUBSTRING

SUBSTRING函数可以对字符串进行子串的截取操作。

1．语法结构

        SUBSTRING ( expression , start , length )

语法中主要参数说明如下。

● Expression：是字符串、二进制字符串、文本、图像、列或包含列的表达式，但不要使用包含聚合函数的表达式。

● Start：指定子字符串开始位置的整数。start可以为bigint类型。

● Length：一个正整数，指定要返回的expression的字符数或字节数。如果length为负，则会返回错误。length可以是bigint数据类型。

2．返回值

● 如果expression是受支持的字符数据类型，则返回字符数据。

● 如果expression是受支持的binary数据类型，则返回二进制数据。

3．实例

下面的代码将返回Contact表中姓名以“Barl”开头人员信息的姓名的首字符。

        USE AdventureWorks;
        GO
        SELECT C.LastName as ’姓氏’, SUBSTRING(C.FirstName, 1, 1) AS ’姓名首字符’
        FROM Person.Contact C
        WHERE C.LastName like ' Barl%'
        ORDER BY C.LastName

源代码文件：\代码\0144.sql。

1.8.9 获取字符串长度LEN

LEN函数获取字符串的长度（字符数），但不包括右边的空格，左边的空格和中间的空格计算在内。

1．语法结构

        LEN ( string_expression )

String_expression：要计算长度的字符串。

2．返回值

● expression数据类型为varchar(max)、nvarchar(max)或varbinary(max)，则为bigint。

● 否则为int。

3．实例

下面的代码将分别测试空格在不同位置时返回的字符串的长度。

        DECLARE @TempString varchar(100)
        DECLARE @TempString1 varchar(100)
        DECLARE @TempString2 varchar(100)
        DECLARE @TempString3 varchar(100)
        --不包含空格的字符串
        SET  @TempString=' dancewithwave@163.com'
        --左边有个空格的字符串
        SET  @TempString1='  dancewithwave@163.com'
        --右边有个空格的字符串
        SET  @TempString2=' dancewithwave@163.com  '
        --左右都有个空格的字符串
        SET  @TempString3='  dancewithwave@163.com  '
        SELECT LEN(@TempString)
        SELECT LEN(@TempString1)
        SELECT LEN(@TempString2)
        SELECT LEN(@TempString3)

源代码文件：\代码\0145.sql。

1.8.10 替换字符串中内容STUFF

STUFF函数用于在指定的字符串中删除指定长度的字符，并在起点处插入另外一组字符。

1．语法结构

        STUFF(Source_character_expression,
              start ,
              length ,
              Destination_character_expression)

语法中主要参数说明如下。

● Source_character_expression：源字符串。可以是常量、变量，也可以是字符列或二进制数据列。

● Start：一个整数值，指定删除和插入的开始位置。如果start或length为负，则返回空字符串。如果start比第一个character_expression长，则返回空字符串。start可以是bigint类型。

● Length：一个整数，指定要删除的字符数。如果length比第一个character_expression长，则最多删除到最后一个character_expression中的最后一个字符。length可以是bigint类型。

● Destination_character_expression：目的字符串。可以是常量、变量，也可以是字符列或二进制数据列。将在源字符串中执行插入。

2．返回值

如果character_expression是受支持的字符数据类型，则返回字符数据。如果character_expression是一个受支持的binary数据类型，则返回二进制数据。

3．实例

下面的代码将删除从1到13的字符串中的字符，然后用字符串“zhaosongtao”进行替换。

        SELECT STUFF(' dancewithwave@163.com' , 1, 13, ' zhaosongtao' )
        GO

源代码文件：\代码\0146.sql。

1.8.11 指定位置搜索字符串中内容CHARINDEX

CHARINDEX函数用于在指定的字符串中搜索特定的字符串，并可以指定开始搜索的位置，返回第一次找到目标字符串的字符数。

1．语法结构

        CHARINDEX ( expression1 , expression2 [ , start_location ] )

语法中参数说明如下。

● expression1：一个字符串数据类型的表达式，其中包含要查找的字符的序列。

● expression2：一个字符串数据类型的表达式，通常是一个为指定序列搜索的列。

● start_location：开始在expression2中搜索expression1时的字符位置。如果start_location未被指定、是一个负数或零，则将从expression2的开头开始搜索。start_location可以是bigint类型。

2．返回值

如果expression2的数据类型为varchar(max)、nvarchar(max)或varbinary(max)，则为bigint，否则为int。

3．实例

下面的代码将定义一个要搜索的字符串@DestinationTempString，然后在源字符串@SourceTempString中进行搜索，返回第一次找到“A”字符字符数。

        DECLARE @SourceTempString varchar(200)
        DECLARE @DestinationTempString varchar(10)
        SET  @SourceTempString=' ASFHJASGFQWIGTPJGSAGHPSAFHRU[WEROFHW  FWEOFIEWHJ
    EWROIPERF WEWPIORFJEWRI'
        SET @DestinationTempString=' A'
        SELECT CHARINDEX(@DestinationTempString, @SourceTempString,1)

源代码文件：\代码\0147.sql。

1.8.12 生成带分隔符的Unicode字符串QUOTENAME

QUOTENAME函数用于生成带有分隔符的Unicode字符串。

1．分隔符

可以是单引号(' )、左方括号或右方括号([ ])或者英文双引号(")。如果未指定，则使用方括号。

带有分隔符的Unicode字符串的例子如下。

[dancewithwave]

2．语法结构

        QUOTENAME ( ' character_string' [ , ' quote_character' ] )

其中的参数说明如下。

● Character_string:Unicode字符数据构成的字符串。

● Quote_character：用作分隔符的单字符字符串。

3．返回值

nvarchar(258)，生成的带有分隔符的字符串长度不能超过258。

4．实例

下面的代码定义生成带有分隔符的字符串。结果中对特定的“]”（右方括号）需要两个，是因为该符号为特定的转义符，需要用两个符号来表明该符号本身也是字符串的一部分。

        SELECT QUOTENAME(' abc[]def' )
        GO

源代码文件：\代码\0148.sql。

1.8.13 转换浮点数字为字符串STR

STR函数用于将浮点数据转换为字符串。

1．语法结构

STR(float_expression[, length[, decimal]])

语法中参数说明如下。

● float_expression：带小数点的近似数字(float)数据类型的表达式。

● Length：总长度。它包括小数点、符号、数字以及空格。默认值为10。

● Decimal：小数点后的位数。decimal必须小于或等于16。如果decimal大于16，则会截断结果，使其保持为小数点后只有16位。

2．返回值

Char，定长字符串。

3．实例

下面的代码将转换浮点数字为字符串后和其他字符串执行连接操作。

        SELECT STR(123.457, 7, 3)+SPACE(2)+' abcd'
        GO

源代码文件：\代码\0149.sql。

1.8.14 截取左边字符串LEFT

LEFT函数用于截取从左边第一个字符开始，指定长度的字符串。其执行效果等于SUBSTRING(expression,1, length)。

1．语法结构

        LEFT(character_expression, integer_expression)

语法中参数说明如下。

● Character_expression：字符或二进制表达式，可以是常量、变量或表达式。

● Integer_expression：正整数，指定返回的字符数。

2．返回值

Varchar或nvarchar，变长字符串。

3．实例

下面的代码截取Product表的Name字段的左边5个字符，采用两种方法截取字符串的结果是完全一致的。

        USE AdventureWorks;
        GO
        SELECT LEFT(P.Name, 5) , SUBSTRING(P.NAME,1,5)
        FROM Production.Product P
        ORDER BY P.ProductID ;
        GO

源代码文件：\代码\0150.sql。

1.8.15 截取右边字符串RIGHT

RIGHT函数用于截取从右边第一个字符开始，指定长度的字符串。其执行效果等于SUBSTRING(expression, LEN(expression)-length+1, length)。

1．语法结构

        RIGHT(character_expression, integer_expression)

语法中参数说明如下。

● Character_expression：字符或二进制表达式，可以是常量、变量或表达式。

● Integer_expression：正整数，指定返回的字符数。

2．返回值

Varchar或nvarchar，变长字符串。

3．实例

下面的代码截取Product表的Name字段的右边5个字符，采用两种方法截取字符串的结果是完全一致的。

        USE AdventureWorks;
        GO
        SELECT RIGHT(P.Name, 5) , SUBSTRING(P.NAME, LEN(P.NAME)-5+1,5)
        FROM Production.Product P
        ORDER BY P.ProductID ;
        GO

源代码文件：\代码\0151.sql。

1.8.16 截取左边空格LTRIM

如果字符串的第一个字符为空格，LTRIM函数用于截取连续的左边空格。

1．语法结构

        LTRIM(character_expression)

Character_expression为字符或二进制数据表达式，可以是常量、变量或数据列。

2．返回值

Varchar或nvarchar，变长字符串。

3．实例

下面的代码将截取左边的空格。

        DECLARE @LtrimTempString varchar(60)
        SET @LtrimTempString = '  dancewithwave@163.com  zhaosongtao@pku.org.cn'
        SELECT LEN(@LtrimTempString)
        SELECT  LTRIM(@LtrimTempString)
        SELECT  LEN(LTRIM(@LtrimTempString))
        GO

源代码文件：\代码\0152.sql。

1.8.17 截取右边空格RTRIM

RTRIM函数用于截取连续的右边空格。

1．语法结构

        RTRIM(character_expression)

Character_expression为字符或二进制数据表达式，可以是常量、变量或数据列。

2．返回值

Varchar或nvarchar，变长字符串。

3．实例

下面的代码将截取右边的空格。

        DECLARE @RtrimTempString varchar(60)
        SET @RtrimTempString = '  dancewithwave@163.com  zhaosongtao@pku.org.cn '
        SELECT  RTRIM(@RtrimTempString)
        GO

源代码文件：\代码\0153.sql。

1.8.18 转换为小写字符串LOWER

LOWER函数将字符串全部转换为小写字符后返回。

1．语法结构

        LOWER(character_expression)

Character_expression为字符或二进制数据表达式，可以是常量、变量或数据列。

2．返回值

Varchar或nvarchar，变长字符串。

3．实例

下面的代码将字符串全部转换为小写。

        DECLARE @TempString varchar(60)
        SET @TempString = ' hELLO wORLD! '
        SELECT  LOWER(@TempString)
        GO

源代码文件：\代码\0154.sql。

1.8.19 转换为大写字符串UPPER

UPPER函数将字符串全部转换为大写字符后返回。

1．语法结构

        UPPER(character_expression)

Character_expression为字符或二进制数据表达式，可以是常量、变量或数据列。

2．返回值

Varchar或nvarchar，变长字符串。

3．实例

下面的代码将字符串全部转换为大写。

        DECLARE @TempString varchar(60)
        SET @TempString = ' hELLO wORLD! '
        SELECT  UPPER(@TempString)
        GO

源代码文件：\代码\0155.sql。

1.8.20 反序字符串REVERSE

REVERSE函数将字符串内容反序后返回。

1．语法结构

        REVERSE(character_expression)

Character_expression为字符或二进制数据表达式，可以是常量、变量或数据列。

2．返回值

Varchar或nvarchar，变长字符串。

3．实例

下面的代码将字符串反序后返回。

        DECLARE @TempString varchar(60)
        SET @TempString = ' hELLO wORLD! '
        SELECT  REVERSE(@TempString)
        GO

源代码文件：\代码\0156.sql。

1.8.21 获取字符串字节数DATALENGTH

DATALENGTH函数获取字符串的字节数，而不是字符数。该函数不仅仅适合字符串数据，还适合文本（text、ntext）、二进制（varbinary、binary）和图像（image）等任意类型的数据。

1．语法结构

        DATALENGTH(expression)

2．返回值

如果expression数据类型为varchar(max)、nvarchar(max)或varbinary(max)数据类型，则返回bigint；否则返回int。

3．实例

下面的代码返回字段Name的字节数和字符数。

        USE AdventureWorks;
        GO
        SELECT  P.Name, Bytelength = DATALENGTH(P.Name), Characterlength = LEN(P.Name)
        FROM Production.Product P
        ORDER BY P.Name;
        GO

源代码文件：\代码\0157.sql。

上一章目录下一章