[sql] 常用函数整理(T-SQL 版)

2017-09-05 19:17:39来源:cnblogs.com作者:骨折仔(原名反骨仔)人点击

分享
USE master;--5.2 字符串函数--1.计算字符串长度SELECT LEN('abc');--2.字符串转换为小写SELECT 'A',    LOWER('a');--3.字符串转换为大写SELECT 'a',    UPPER('a');--4.截去字符串左侧空格SELECT ' a ',    LTRIM(' a ');--5.截去字符串右侧空格SELECT ' a ',    RTRIM(' a ');--6.截去字符串两侧的空格SELECT ' a ',    RTRIM(LTRIM(' a '));--7.取子字符串SELECT 'abcdef',    SUBSTRING('abcdef', 2, 3);--8.计算子字符串的位置SELECT 'abcdef',    CHARINDEX('abcdef', 'abcdef');--9.从左侧开始取子字符串SELECT 'abcdef',    LEFT('abcdef', 3);--10.从右侧开始取子字符串SELECT 'abcdef',    RIGHT('abcdef', 3);--11.字符串替换SELECT REPLACE(' abc def', ' ', '');--12.得到字符的 ASCII 码SELECT ASCII('a'),    ASCII('abc');--13.得到一个 ASCII 码数字对应的字符SELECT CHAR(56),    CHAR(90),    CHAR(ASCII('a'));--转换为整数SELECT CEILING(90.021);--14.发音匹配度SELECT SOUNDEX('jack'),    SOUNDEX('jeck'),    SOUNDEX('joke'),    SOUNDEX('juke'),    SOUNDEX('look'),    SOUNDEX('jobe');SELECT DIFFERENCE('Kerry', 'Merry');--5.3 日期时间函数--1.取得当前日期时间的函数为 GETDATE()SELECT GETDATE();SELECT CONVERT(VARCHAR(50), GETDATE(), 101) AS 当前日期,    CONVERT(VARCHAR(50), GETDATE(), 108) AS 当前时间;--日期增减SELECT GETDATE(),    DATEADD(YEAR, 3, GETDATE()) AS threeyrs,    DATEADD(QUARTER, 20, GETDATE()) AS ttqutrs,    DATEADD(MONTH, 68, GETDATE()) AS sxtmonths,    DATEADD(WEEK, -1000, GETDATE()) AS thweeik;--计算日期差额SELECT GETDATE(),    DATEDIFF(YEAR, '1992-06-01 00:00:00', GETDATE()) '年份',    DATEDIFF(QUARTER, '1992-06-01 00:00:00', GETDATE()) '季度',    DATEDIFF(MONTH, '1992-06-01 00:00:00', GETDATE()) '月份',    DATEDIFF(DAYOFYEAR, '1992-06-01 00:00:00', GETDATE()) '每年的某一日',    DATEDIFF(DAY, '1992-06-01 00:00:00', GETDATE()) '日期',    DATEDIFF(WEEK, '1992-06-01 00:00:00', GETDATE()) '星期',    DATEDIFF(WEEKDAY, '1992-06-01 00:00:00', GETDATE()) '工作日',    DATEDIFF(HOUR, '1992-06-01 00:00:00', GETDATE()) '小时',    DATEDIFF(MINUTE, '1992-06-01 00:00:00', GETDATE()) '分钟',    DATEDIFF(SECOND, '1992-06-01 00:00:00', GETDATE()) '秒';-- 5.3.6 计算一个日期是星期几SELECT GETDATE(),    DATENAME(YEAR, GETDATE()) '年份',    DATENAME(QUARTER, GETDATE()) '季度',    DATENAME(MONTH, GETDATE()) '月份',    DATENAME(DAYOFYEAR, GETDATE()) '每年的某一日',    DATENAME(DAY, GETDATE()) '日期',    DATENAME(WEEK, GETDATE()) '星期',    DATENAME(WEEKDAY, GETDATE()) '工作日',    DATENAME(HOUR, GETDATE()) '小时',    DATENAME(MINUTE, GETDATE()) '分钟',    DATENAME(SECOND, GETDATE()) '秒',    DATENAME(MILLISECOND, GETDATE()) '毫秒';-- 5.3.7 取得日期的指定部分SELECT GETDATE(),    DATEPART(YEAR, GETDATE()) '年份',    DATEPART(QUARTER, GETDATE()) '季度',    DATEPART(MONTH, GETDATE()) '月份',    DATEPART(DAYOFYEAR, GETDATE()) '每年的某一日',    DATEPART(DAY, GETDATE()) '日期',    DATEPART(WEEK, GETDATE()) '星期',    DATEPART(WEEKDAY, GETDATE()) '工作日',    DATEPART(HOUR, GETDATE()) '小时',    DATEPART(MINUTE, GETDATE()) '分钟',    DATEPART(SECOND, GETDATE()) '秒',    DATEPART(MILLISECOND, GETDATE()) '毫秒';-- DATEPART() 函数的返回值是数字而 DATENAME() 函数则会将尽可能的以名称的方式做为返回值-- 5.4 其他函数-- 5.4.1 类型转换--CAST ( expression AS data_type)--CONVERT ( data_type, expression)SELECTCAST('-30' AS INT) as i,CONVERT(DECIMAL,'3.1415726') as d,CONVERT(DATETIME,'2008-08-08 08:09:10') as dt-- 5.4.2 空值处理-- 5.4.2.1 COALESCE()函数-- COALESCE ( expression,value1,value2……,valuen)SELECT COALESCE('', GETDATE()),    COALESCE(NULL, GETDATE()),    COALESCE(NULL, '2008-08-08',GETDATE())

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台