SQL字符串函數

四捨五入函數:

select round(0.442499,3)
select cast(round(0.442499,3) as numeric(10,3))

包含子字符串函数

charindex和patindex函数经常用来在一段字符中搜索字符或字符串。假如被搜索的字符中包含有要搜索的字符，那么这两个函数返回一个非零的整数，这个整数是要搜索的字符在被搜索的字符中的开始位数。patindex函数支持使用通配符来进行搜索，然而charindex不支持通配符

参考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：介于 0 和 255 之间的整数。如果整数表达式不在此范围内，将返回 NULL 值。

返回类型：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'K

benhavn' --‘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')

返回结果：

----- -----

S252 S200

例2：

DIFFERENCE 函数比较 SOUNDEX 模式结果的差。第一个示例显示两个仅元音不同的字符串。返回的差是 4（可能的最小差）。

SELECT DIFFERENCE('Smithers', 'Smythers')

返回结果：

-----------

例3：

在下例中，字符串的辅音不同，所以返回的差是 2（较高的差）。

SELECT DIFFERENCE('Anothers', 'Brothers')

返回结果：

-----------

}

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

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'

返回结果：

-----------

如果未通过 WHERE 子句限制要搜索的行，查询将返回表中的所有行，对在其中找到该模式的所有行报告非零值，对在其中未找到该模式的所有行报告零值。

例2：使用通配符查找模式"won_erful"在 titles 表中 notes 列的某一特定行中的开始位置，其中下划线为代表任何字符的通配符。

USE pub

SELECT PATINDEX('%won_erful%', notes) FROM titles WHERE title_id = 'TC3218'

返回结果：

------------

如果没有限制要搜索的行，查询将返回表中的所有行，对在其中找到该模式的所有行报告非零值。

}

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'

返回结果：

-----------

例2、使用可选的 start_location 参数从 notes 列的第五个字符开始寻找"wonderful"。

USE pubs

SELECT CHARINDEX('wonderful', notes, 5) FROM titles WHERE title_id = 'TC3218'

返回结果：

-----------

例3、显示了当 expression2 内找不到 expression1 时的结果集。

USE pubs

SELECT CHARINDEX('wondrous', notes) FROM titles WHERE title_id='TC3218'

返回结果：

-----------

}

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 ( '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

}

例子：

知: 字段A='F:/photo/Winter Leaves.jpg'
要求:分段截取每段字符[字段A不能为TEXT类型,否则报错]

解决方法:

---截取字符串A的第一个/左边的字符串
select left(A,charindex('/',A)-1)
输出结果：F:

---截取/中间的字符串
select left(stuff(A,1,charindex('/',A),''),charindex('/',stuff(A,1,charindex('/',A),''))-1)
输出结果：photo

---截取最后一个/后面的字符串
select reverse(left(reverse(A),charindex('/',reverse(A))-1))
输出结果：Winter Leaves.jpg

---截取字符串A的首字幕
select STUFF(A,1, 1, '')
输出结果：:/photo/Winter Leaves.jpg

使用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。

posted on 2013-05-13 16:02 blue.net 阅读(331) 评论(0) 收藏举报

刷新页面返回顶部

BLUE.NET

公告