字符串函数
一、字符转换函数
1、ASCII() 返回字符表达式最左端字符的ASCII 码值。在ASCII()函数中,纯数字的字符串可不用‘’括起来,但含其它字符的字符串必须用‘’括起来使用,否则会出错。
2、CHAR() 将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR() 返回NULL 。
3、LOWER()和UPPER() LOWER()将字符串全部转为小写;UPPER()将字符串全部转为大写。
4、STR() 把数值型数据转换为字符型数据。 STR ([,length[, ]]) length 指定返回的字符串的长度,decimal 指定返回的小数位数。如果没有指定长度,缺省的length 值为10, decimal 缺省值为0。当length 或者decimal 为负值时,返回NULL;当length 小于小数点左边(包括符号位)的位数时,返回length 个*;先服从length ,再取decimal ;当返回的字符串位数小于length ,左边补足空格。
二、去空格函数
1、LTRIM() 把字符串头部的空格去掉。
2、RTRIM() 把字符串尾部的空格去掉。
三、取子串函数
1、left() LEFT (, ) 返回character_expression 左起 integer_expression 个字符。
2、RIGHT() RIGHT (, ) 返回character_expression 右起 integer_expression 个字符。
3、SUBSTRING() SUBSTRING (, , length) 返回从字符串左边第starting_ position 个字符起length个字符的部分。
四、字符串比较函数
1、CHARINDEX() 返回字符串中某个指定的子串出现的开始位置。 CHARINDEX (<’substring_expression’>, ) 其中substring _expression 是所要查找的字符表达式,expression 可为字符串也可为列名表达式。如果没有发现子串,则返回0 值。此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX() 返回字符串中某个指定的子串出现的开始位置。 PATINDEX (<’%substring _expression%’>, )其中子串表达式前后必须有百分号“%”否则返回值为0。与CHARINDEX 函数不同的是,PATINDEX函数的子串中可以使用通配符,且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。
五、字符串操作函数
1、QUOTENAME() 返回被特定字符括起来的字符串。 QUOTENAME (<’character_expression’>[, quote_ character]) 其中quote_ character 标明括字符串所用的字符,缺省值为“[]”。
2、REPLICATE() 返回一个重复character_expression 指定次数的字符串。 REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值,则返回NULL 。
3、REVERSE() 将指定的字符串的字符排列顺序颠倒。 REVERSE () 其中character_expression 可以是字符串、常数或一个列的值。
4、REPLACE() 返回被替换了指定子串的字符串。 REPLACE (, , ) 用string_expression3 替换在string_expression1 中的子串string_expression2。
5、SPACE() 返回一个有指定长度的空白字符串。 SPACE () 如果integer_expression 值为负值,则返回NULL 。
6、STUFF() 用另一子串替换字符串指定位置、长度的子串。 STUFF (, , ,) 如果起始位置为负或长度值为负,或者起始位置大于character_expression1 的长度,则返回NULL 值。如果length 长度大于character_expression1 中 start_ position 以右的长度,则character_expression1 只保留首字符。
六、数据类型转换函数
1、CAST() CAST ( AS [ length ]) 2、CONVERT() CONVERT ([ length ], [, style]) 1)data_type为SQL Server系统定义的数据类型,用户自定义的数据类型不能在此使用。
2)length用于指定数据的长度,缺省值为30。
3)把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4)TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符,即CHAR或VARCHAR数据类型是最大长度。
5)IMAGE类型存储的数据转换到BINARY或VARBINARY类型,最多为8000个字符。
6)把整数值转换为MONEY或SMALLMONEY类型,按定义的国家的货币单位来处理,如人民币、美元、英镑等。
7)BIT类型的转换把非零值转换为1,并仍以BIT类型存储。
8)试图转换到不同长度的数据类型,会截短转换值并在转换值后显示“+”,以标识发生了这种截断。
9)用CONVERT() 函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号,不同的样式编号有不同的输出格式。
/* 返回字符表达式中最左侧字符的ASCII代码值 */ select Ascii('a') --a:97,A:65
/* 将整数ASCII代码转换为字符 */ select Char(97)--97:a,65:A
/* 返回表达式中指定字符的开始位置 */ select Charindex('b','abcdefg',5)
/* 以整数返回两个字符表达式的SOUNDEX值之差 */ select Difference('bet','bit')—3
/* 返回字符表达式最左侧指定数目的字符 */ select Left('abcdefg',3)—abc
/* 返回给定字符串表达的字符数 */ select Len('abcdefg')—7
/* 返回将大写字符转换为小字符的字符表达式 */ select Lower('ABCDEFG')—abcdefg
/* 返回删除了前导空格之后字符表达式 */ select Ltrim(' abcdefg')—abcdefg
/* 返回具有给定的整数代码的UNICODE字符 */ select Nchar(65)—A
/* 返回指定表达式中模式第一次出现的开始位置 */ select Patindex('%_cd%','abcdefg')—2
/* 返回为成为有效的SQL SERVER分隔标识符而添加了分隔符的UNICODE字符串 */
select Quotename('create table')
/* 用第三个表达式替换第一个表达式中出现的第二个表达式 */
select Replace('abcdefg','cd','xxx')--abxxxefg
/* 按指定次数重复表达式 */ select Replicate('abc|',4)--abc|abc|abc|abc|
/* 返回字符表达式的逆向表达式 */ select Reverse('abc')—cba
/* 返回字符表达式右侧指定数目的字符 */ select Right('abcd',3)—bcd
/* 返回截断了所有尾随空格之后的字符表达式 */ select Rtrim('abcd ')—abcd
/* 返回由四个字符表达的SOUNDEX代码 */ select Soundex('abcd')--A120
/* 返回由重复空格组成的字符串 */ select Space(10)--[ ]
/* 返回从默认表达转换而来的字符串 */ select Str(100)--[ 100]
select Str(100,3)--[100] select Str(14.4444,5,4)--[14.44]
/* 删除指定长度的字符,并在指定的起点处插入另一组字符 */
select Stuff('abcdefg',2,4,'xxx')—axxxfg
/* 返回字符表达式,二进制,文本表达式或图像表达的一部分 */ select Substring('abcdefg',2,3)—bcd
/* 返回表达第一个字符的UNICODE整数值 */ select Unicode('a')—97
/* 返回将小写字符转换为大写字符的字符表达式 */ select Upper('a')--'A'
日期函数…
1 SQL Server 有两种日期类型:DATETIME 和 SMALLDATETIME
DATETIME 的日期范围:1753-1-1到9999-12-31之间的日期值,精度为3.33毫秒,其类型的值在SQLServer内部用两个 4 字节的整数存储。
第一个 4 字节存储“基础日期”(即 1900 年 1 月 1 日)之前或之后的天数。基础日期是系统参照日期。
另外一个 4 字节存储天的时间(以午夜后经过的毫秒数表示)。
SMALLDATETIME的范围:1900-1-1到2079-6-6之间的日期值,精度为1分钟,其类型的值在SQLServer内部用两个 2 字节的整数存储。
第一个 2 字节存储 1900 年 1 月 1 日后的天数。另外一个 2 字节存储午夜后经过的分钟数
2 SQL Server 获得当前日期的函数: GETDATE() GETUTCDATE() CURRENT_TIMESTAMP
GETDATE(): 返回当前系统日期和时间
GETUTCDATE(): 使用 DT_DBTIMESTAMP 格式返回以 UTC 时间(通用协调时间或格林尼治标准时间)表示的系统当前日期
CURRENT_TIMESTAMP: 返回当前日期的和时间,等价于GETDATE()函数.注此函数不带括号"()"
-----------------------------------------------------------------------------------------------------------------
SELECT GETDATE() AS [GETDATE],CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],GETUTCDATE() AS [GETUTCDATE] ---
--- 返回结果:GETDATE CURRENT_TIMESTAMP GETUTCDATE ---
--- 2008-12-04 18:28:50.437 2008-12-04 18:28:50.437 2008-12-04 10:28:50.437 ---
-----------------------------------------------------------------------------------------------------------------
3 SQL Server 日期抽取函数: DATEPART DATENAME DAY MONTH YEAR
DATENAME: 返回表示指定日期的指定日期部分的字符串语法:DATENAME ( datepart ,date ) 注:datepart是指定要返回的日期部分的参数
DATEPART: 返回表示指定日期的指定日期部分的整数 语法: DATEPART ( datepart ,date ) 注:datepart是指定要返回的日期部分的参数
DAY: 返回一个整数,表示日期的“日”日期部分 语法: DAY(date)
MONTH: 返回一个整数,表示日期的“月”日期部门 语法:MONTH(date)
YEAR: 返回一个整数,表示日期的“年”日期部门 语法:YEAR(date)
注:DAY、MONTH、和 YEAR 函数分别是 DATEPART(dd, date)、DATEPART(mm, date) 和 DATEPART(yy, date) 的同义词, DAY、MONTH、YEAR比DATEPART和DATENAME更简洁
4 SQL Server 日期的加减函数: DATEDIFF DATEADD
DATEDIFF: 返回跨两个指定日期的日期边界数和时间边界数, 语法:DATEDIFF ( datepart , startdate , enddate ) 用 enddate 减去 startdate
注:datepart 指定应在日期的哪一部分计算差额的参数,其日期相减时,只关注边界值,例SELECT DATEDIFF(YEAR,'2008-12-31','2009-1-1') 返回 1
DATEADD : 返回给指定日期加上一个时间间隔后的新 datetime 值。 语法:DATEADD (datepart , number, date )
注: datepart 指定要返回新值的日期的组成部分
number 使用来增加 datepart 的值。正数表示增加,负数表示减少,如果是小数则忽略小数部分,且不做四舍五入。
通过 DATEDIFF 和 DATEADD 的一些日期计算
1) 一年的第一天
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)
注:首先DATEDIFF(YEAR,0,GETDATE()) --计算当前年份与 1900年相差的年数,然后通过计算1900-1-1加上相差的年数的日期即为当年第一天
2) 一个季的第一天
SELECT DATEADD(Quarter,DATEDIFF(Quarter,0,GETDATE()),0)
注:首先DATEDIFF(Quarter,0,GETDATE()) --计算当前月份与 1900年相差的季份数,然后通过计算1900-1-1加上相差的季份数的日期即为当季第一天
3) 一个月的第一天
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
注:首先DATEDIFF(MONTH,0,GETDATE()) --计算当前月份与 1900年相差的月份数,然后通过计算1900-1-1加上相差的月份数的日期即为当月第一天
4) 一周的第一天
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
5) 当天的半夜 (00:00:00.000)
SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
6) 上月的最后一天
SELECT DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))
注:用本月的第一天减去3毫秒,即得出上个月的最有一天.SQL SERVER DATETIME类型的时间精确到3毫秒。
7) 本月的最后一天
SELECT DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0))
8) 本月的天数
i) SELECT DAY(DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)))
ii)SELECT 32-DAY(GETDATE()+(32-DAY(GETDATE())))
9) 本年的最后一天
SELECT DATEADD(ms,-3,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())+1,0))
10) 一周的第一天
SELECT DATEADD(DAY,1-DATEPART(weekday,GETDATE()),GETDATE())
一周的最后一天
SELECT DATEADD(DAY,7-DATEPART(WeekDay,GETDATE()),GETDATE())
SELECT DATEADD(weekday,DATEDIFF(weekday,0,DATEADD(DAY,6-DATEPART(day,GETDATE()),GETDATE())),0)
5 日期转换函数 CONVERT CAST
CONVERT 中的 Style. 参数:108 和 114 可以只得到时间。
例 SELECT CONVERT(NVARCHAR(12),GETDATE(),108) ---12:41:15
SELECT CONVERT(NVARCHAR(12),GETDATE(),114) ---12:43:12:590
6 日期判断函数 ISDATE() 确定输入表达式是否为有效日期。若有效返回 1 否则返回 0 ,返回值为 INT 。