SQL字符串函数
参考MSSQL帮助
1、SUBSTRING
substring函数：
{
返回值：返回字符、binary、text 或 image 表达式的一部分。
语法：SUBSTRING ( expression , start , length )
参数：
expression：是字符串、二进制字符串、text、image、列或包含列的表达式。不要使用包含聚合函数的表达式。
start：是一个整数，指定子串的开始位置。
length：是一个整数，指定子串的长度（要返回的字符数或字节数）。
返回类型：
1)、如果 expression 是支持的字符数据类型，则返回字符数据。
2)、如果 expression 是支持的 binary 数据类型，则返回二进制数据。
返回字符串的类型与给定表达式的类型相同（表中显示的除外）。
| 给定的表达式 | 返回类型 |
|---|---|
| text | varchar |
| image | varbinary |
| ntext | nvarchar |
在字符数中必须指定使用 ntext、char 或 varchar 数据类型的偏移量（start 和 length）。在字节数中必须指定使用 text、image、binary 或 varbinary 数据类型的偏
移量。
}
例:
1)、
substring(convert(varchar(20),getdate(),112),1,4) 值为getdate()的前四个字符:2008
substring(convert(varchar(20),getdate(),112),1,6) 值为getdate()的前六个字符：200807
注意：SUBSTRING ( expression , start , length ) 如果是从第一个字符开始，应该写‘1’。（如果写‘0’，则取的数据将可能不正确）
例：
想取2008则SQL语句应为：
substring(convert(varchar(20),getdate(),112),0,3) （0-3取四个字符，显然返回的并不是我们想要的“20”，而不是“2008”）
2、ASCII、CHAR、NCHAR
ASCII函数：
{
返回值：返回字符表达式最左端字符的 ASCII 代码值。
语法：ASCII ( character_expression )。
参数：character_expression 是类型为 char 或 varchar的表达式。
返回类型：int
}
CHAR函数：
{
返回值：将 int ASCII 代码转换为字符的字符串函数。
语法：CHAR ( integer_expression )。
参数：
integer_expression
返回类型：char(1)
注释：CHAR 可用于将控制字符插入字符串中。
下表显示了一些常用的控制字符：
控制字符
值
制表符
CHAR(9)
换行符
CHAR(10)
回车
CHAR(13)
}
UNICODE函数：
{
返回值：按照 Unicode 标准的定义，返回输入表达式的第一个字符的整数值。
语法：UNICODE ( 'ncharacter_expression' )
参数：'ncharacter_expression' 是 nchar 或 nvarchar 表达式。
返回类型：int
例：
下面的示例使用 UNICODE 和 NCHAR 函数打印 Åkergatan 24 字符串中第一个字符的 UNICODE 值，并打印实际的第一个字符 Å
DECLARE @nstring nchar(12)
SET @nstring = N'Åkergatan 24'
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring))
}
NCHAR函数：
{
返回值：根据 Unicode 标准所进行的定义，用给定整数代码返回 Unicode 字符。
语法：NCHAR ( integer_expression )
参数：integer_expression 介于 0 与 65535 之间的所有正整数。如果指定了超出此范围的值，将返回 NULL。
返回类型：nchar(1)
例：
下面的示例使用 UNICODE 和 NCHAR 函数打印字符串 Køenhavn 第二个字符的 UNICODE 值和 NCHAR（Unicode 字符），并打印实际的第二个字符ø。
DECLARE @nstring nchar(8)ø
SET @nstring = N'Kbenhavn' --‘N’防止显示乱码字符（如果不加‘N’，则显示结果为 248 ?）
SELECT UNICODE(SUBSTRING(@nstring, 2, 1)) AS [UNICODE], NCHAR(UNICODE(SUBSTRING(@nstring, 2, 1))) AS [NCHAR]
返回结果：
UNICODE NCHAR
248 ø
}
示例
1)、下例假定在 ASCII 字符集环境下运行，它将返回字符串"Du Monde entier"中每一个字符的 ASCII 值和 char 字符。
DECLARE @position int, @string char(15)
SET @position = 1
SET @string = 'Du monde entier'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
2)、
DECLARE @STR VARCHAR(50)
SELECT @STR='啊!0CD' --注意：当@STR中存在中文字符的时候，CHAR函数将取空值
select ASCII(SUBSTRING(@STR,1,1)) AS ASCII,CHAR(ASCII(SUBSTRING(@STR,1,1))) AS CHAR,SUBSTRING(@STR,1,1) AS FirstChar
返回结果：
ASCII CHAR FirstChar
176 啊
3)、使用CHAR插入控制字符
下例使用 CHAR(13) 在不同的行上打印名称、地址与城市信息，并以文本方式返回结果。
USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1
3、datalength、len
LEN函数：
{
返回值：返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随、最前面的空格。
语法：LEN ( string_expression )
参数：string_expression 要计算的字符串表达式。
返回类型：int
}
DATALENGTH函数：
{
返回值：返回任何表达式所占用的字节数。
语法：DATALENGTH ( expression )
参数：expression 任何类型的表达式。 }
返回类型：int
注释：
1)、DATALENGTH 对 varchar、varbinary、text、image、nvarchar 和 ntext 数据类型特别有用，因为这些数据类型可以存储可变长度数据。
2)、NULL 的 DATALENGTH 的结果是 NULL。
例：
DECLARE @STR VARCHAR(50)
SELECT @STR=' 啊!0CD'
select datalength(@STR) AS [datalength],len(@str) AS [len]
返回值：
datalength len
7 6
4、SOUNDEX、DIFFERENCE
SOUNDEX函数
{
返回值：返回由四个字符组成的代码 (SOUNDEX) 以评估两个字符串的相似性。
语法：SOUNDEX ( character_expression )
参数：character_expression 是字符数据的字母数字表达式。character_expression 可以是常数、变量或列。
返回类型：char
注释：
SOUNDEX 将 alpha 字符串转换成由四个字符组成的代码，以查找相似的词或名称。
代码的第一个字符是 character_expression 的第一个字符，代码的第二个字符到第四个字符是数字。
将忽略 character_expression 中的元音，除非它们是字符串的第一个字母。可以嵌套字符串函数。
例1：
下例显示 SOUNDEX 函数及相关的 DIFFERENCE 函数。
在第一个示例中，返回所有辅音字母的标准 SOUNDEX 值。为 Smith 和 Smythe 返回的 SOUNDEX 结果相同，因为不包括所有元音、字母 y、连写字母和字母 h。
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
返回结果：
----- ----- 3
S252 S200
例2：
DIFFERENCE 函数比较 SOUNDEX 模式结果的差。第一个示例显示两个仅元音不同的字符串。返回的差是 4（可能的最小差）。
SELECT DIFFERENCE('Smithers', 'Smythers')
返回结果：
-----------
例3：
在下例中，字符串的辅音不同，所以返回的差是 2（较高的差）。
SELECT DIFFERENCE('Anothers', 'Brothers')
返回结果：
-----------
2
}
DIFFERENCE函数：
{
返回值：以整数返回两个字符表达式的 SOUNDEX 值之差。
语法：DIFFERENCE ( character_expression , character_expression )
参数：character_expressioncharacter_expression 是类型 char 或 varchar 的表达式。
返回类型：int
注释：返回的整数是 SOUNDEX 值中相同字符的个数。返回的值从 0 到 4 不等，4 表示 SOUNDEX 值相同。
例：
在下例的第一部分，比较两个相差很小的字符串的 SOUNDEX 值，DIFFERENCE 返回的值是 4。
在下例的第二部分，比较两个相差很大的字符串的 SOUNDEX 值，DIFFERENCE 返回的值是 0。
SELECT SOUNDEX('Green'),SOUNDEX('Greene'), DIFFERENCE('Green','Greene')
返回结果：
----- ----- -----------
G160 G160 4
SELECT SOUNDEX('Blotchet-Halls'),SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene'
返回结果：
----- ----- -----------
B124 G160 1
}
5、REPLICATE 重复输出指定次数的字符
REPLICATE函数:
{
返回值:以指定的次数重复字符表达式。
语法：REPLICATE ( character_expression , integer_expression ) 参数：
character_expression：由字符数据组成的字母数字表达式。character_expression 可以是常量或变量，也可以是字符列或二进制数据列。
integer_expression：是正整数。如果 integer_expression 为负，则返回空字符串。
返回类型：varchar character_expression 必须为可隐性转换为 varchar 的数据类型。否则，使用 CAST 函数显式转换 character_expression
注释：兼容级别可能影响返回值。
例1：
select replicate('0',4) AS [replicate]
返回结果：
replicate
0000
例2：
USE pubs 返回结果：
SELECT REPLICATE(au_fname, 2) FROM authors ORDER BY au_fname
----------
AbrahamAbraham
AkikoAkiko
AlbertAlbert
例3：
USE pubs
GO
SELECT SUBSTRING((UPPER(au_lname) + ',' + SPACE(1) + au_fname), 1, 35) AS Name, phone AS Phone, REPLICATE(phone,1) AS Fax
FROM authors ORDER BY au_lname, au_fname
返回结果：
Name Phone Fax
----------------------------------- ------------ -----------------------
BENNET, Abraham 415 658-9932 415 658-9932
BLOTCHET-HALLS, Reginald 503 745-6402 503 745-6402
CARSON, Cheryl 415 548-7723 415 548-7723
}
5、PATINDEX、CHARINDEX
1)、PATINDEX函数：
{
返回值：返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
语法：PATINDEX ( '%pattern%' , expression )
参数：
pattern：一个字符串。可以使用通配符，但 pattern 之前和之后必须有 % 字符（搜索第一个和最后一个字符时除外）。pattern 是短字符数据类型类别的表达式。
expression：一个表达式，通常为要在其中搜索指定模式的列，expression 为字符串数据类型类别。
返回类型：int
注释：
PATINDEX 对 text 数据类型很有用；
除 IS NULL、IS NOT NULL 和 LIKE（这些是 WHERE 子句中对 text 类型有效的仅有的其它比较运算）外，PATINDEX 也可用于 WHERE 子句中。
如果 pattern 或 expression 为 NULL，则当数据库的兼容级别为 70 时 PATINDEX 返回 NULL；
如果数据库兼容级别为 65 或更靠前，则仅当 pattern 和 expression 同时为 NULL 时，PATINDEX 返回 NULL
例1：查找模式"wonderful"在 titles 表中 notes 列的某一特定行中的开始位置。
USE pubs 返回结果：
SELECT PATINDEX('%wonderful%', notes) FROM titles WHERE title_id = 'TC3218'
----------- 返回结果：
46
如果未通过 WHERE 子句限制要搜索的行，查询将返回表中的所有行，对在其中找到该模式的所有行报告非零值，对在其中未找到该模式的所有行报告零值。
例2： 使用通配符查找模式"won_erful"在 titles 表中 notes 列的某一特定行中的开始位置，其中下划线为代表任何字符的通配符。
USE pub
SELECT PATINDEX('%won_erful%', notes) FROM titles WHERE title_id = 'TC3218'
------------
46
如果没有限制要搜索的行，查询将返回表中的所有行，对在其中找到该模式的所有行报告非零值。
}
2)、CHARINDEX函数：
{
返回值：返回字符串中指定表达式的起始位置。
语法：CHARINDEX ( expression1 , expression2 [ , start_location ] )
参数：
expression1：一个表达式，其中包含要寻找的字符的次序。expression1 是一个短字符数据类型分类的表达式。
expression2：一个表达式，通常是一个用于搜索指定序列的列。expression2 属于字符串数据类型分类。
start_location：
在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location，而是一个负数或零，则将从 expression2 的起始位置开始搜索。
返回类型：int
注释：
如果 expression1 或 expression2 之一属于 Unicode 数据类型（nvarchar 或 nchar）而另一个不属于，则将另一个转换为 Unicode 数据类型。
如果 expression1 或 expression2 之一为 NULL 值，则当数据库兼容级别为 70 或更大时，CHARINDEX 返回 NULL 值。当数据库兼容级别为 65 或更小时，CHARINDEX 仅在 expression1 和 expression2 都为 NULL 时返回 NULL 值。
如果在 expression2 内没有找到 expression1，则 CHARINDEX 返回 0。
例1、返回序列"wonderful"在 titles 表的 notes 列中开始的位置。
SELECT CHARINDEX('wonderful', notes) FROM titles WHERE title_id = 'TC3218'
返回结果：
-----------
46
例2、使用可选的 start_location 参数从 notes 列的第五个字符开始寻找"wonderful"。
USE pubs
SELECT CHARINDEX('wonderful', notes, 5) FROM titles WHERE title_id = 'TC3218'
返回结果：
-----------
46
例3、显示了当 expression2 内找不到 expression1 时的结果集。
USE pubs
SELECT CHARINDEX('wondrous', notes) FROM titles WHERE title_id='TC3218'
返回结果：
-----------
0
}
6、SPACE
SPACE函数：
{
返回值：返回由重复的空格组成的字符串。
语法：SPACE ( integer_expression )
参数：integer_expression 是表示空格个数的正整数。如果 integer_expression 为负，则返回空字符串。
返回类型：char
注释：若要在 Unicode 数据中包括空格，请使用 REPLICATE 而非 SPACE。
例：
select 'Hello'+space(3)+'World!' AS value
返回结果：
----------------
Hello World!
}
6、REPLACE
REPLACE 函数：
{
返回值：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
语法：REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
参数：
'string_expression1'：待搜索的字符串表达式。string_expression1 可以是字符数据或二进制数据。
'string_expression2'：待查找的字符串表达式。string_expression2 可以是字符数据或二进制数据。
'string_expression3'：替换用的字符串表达式。string_expression3 可以是字符数据或二进制数据。
例：下例用 'xxxxx'替换 'string values'中的字符串 values。
declare @str varchar(50)
select @str='string values'
select replace(@str,'values','xxxxx') AS replaceStr
返回结果：
---------------
string xxxxx
}
6、STR 数字转换成字符类型
STR函数：
{
描述：由数字数据转换来的字符数据。
语法：STR ( float_expression [ , length [ , decimal ] ] )
参数：
float_expression ：是带小数点的近似数字 (float) 数据类型的表达式。不要在 STR 函数中将函数或子查询用作 float_expression。
length：是总长度，包括小数点、符号、数字或空格。默认值为 10。
decimal：是小数点右边的位数。
返回类型：char
注释：
如果为 STR 提供 length 和 decimal 参数值，则这些值应该是正数。在默认情况下或者小数参数为 0 时，数字四舍五入为整数。指定长度应该大于或等于小数点前面的数字加上
数字符号（若有）的长度。短的 float_expression 在指定长度内右对齐，长的 float_expression 则截断为指定的小数位数。例如，STR(12,10) 输出的结果是 12，在结果
集内右对齐。而 STR(1223, 2) 则将结果集截断为 **。可以嵌套字符串函数。
说明：若要转换为 Unicode 数据，请在 CONVERT 或 CAST 转换函数内使用 STR。
例1：下例将包含五个数字和一个小数点的表达式转换为有六个位置的字符串。数字的小数部分四舍五入为一个小数位。
SELECT STR(123.45, 6, 1)
返回结果：
------
123.5
例2：当表达式超出指定长度时，字符串为指定长度返回 **。
SELECT STR(123.45, 2, 2)
返回结果：
--
**
例3：即使数字数据嵌套在 STR内，结果集也是带指定格式的字符数据。
SELECT STR (FLOOR (123.45), 8, 3) 返回结果：
--------
123.000
例4：下例比较 STR 和 CONVERT 的结果。
SELECT STR(3.147) AS 'STR',STR(3.147, 5, 2) AS '2 decimals',STR(3.147, 5, 3) AS '3 decimals'
返回结果：
STR 2 decimals 3 decimals
---------- ---------- ----------
3 3.15 3.147
SELECT CONVERT(char(1), 3.147) AS 'CHAR(1)',CONVERT(char(3), 3.147) AS 'CHAR(3)',CONVERT(char(5), 3.147) AS 'CHAR(5)'
返回结果：
CHAR(1) CHAR(3) CHAR(5)
------- ------- -------
(null) (null) 3.147
}
7、STUFF
stuff函数：
{
描述：删除指定长度的字符并在指定的起始点插入另一组字符。
语法：STUFF ( character_expression , start , length , character_expression )
参数：
character_expression：由字符数据组成的表达式。character_expression 可以是常量、变量，也可以是字符或二进制数据的列。
start：是一个整形值，指定删除和插入的开始位置。如果 start 或 length 是负数，则返回空字符串。如果 start 比第一个 character_expression 长，则返回空字符串。
length：是一个整数，指定要删除的字符数。如果 length 比第一个 character_expression 长，则最多删除到最后一个 character_expression 中的最后一个字符。
character_expression：待要插入的字符。
返回类型：
如果 character_expression 是一个支持的字符数据类型，则返回字符数据。如果 character_expression 是一个支持的 binary 数据类型，则返回二进制数据。
注释：可以嵌套字符串函数。
例：下例通过在第一个字符串 (abcdef) 中删除从第二个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，创建并返回一个字符串。
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
返回结果：
---------
aijklmnef
}
下例显示如何只返回字符串的一部分。该查询在一列中返回 authors 表中的姓氏，在另一列中返回 authors 表中的名字首字母。
