t-sql学习笔记（函数整理T-SQL版）

t-sql学习笔记（函数整理T-SQL版）SELECT CEILING(56.67) ceilingValue1 CEILING(-56.67) ceilingValue2;　　6.舍入到最小整数SELECT rand(9527) randValue;　　5.舍入到最大整数SELECT POWER(5 -0.5) AS powerValue1 POWER(5 2) AS powerValue2 POWER(5 3) AS powerValue3;　　3.求平方根　　SQRT() 函数是用来计算平方根的函数。该函数接受一个参数，这个参数为待计算平方根的表达式。SELECT SQRT(9) AS sqrtValue1 SQRT(3) AS sqrtValue2;　　4.求随机数

一、数学函数

　　1.求绝对值

　　ABS() 函数用来返回一个数值的绝对值。

SELECT ABS(-5.38) AS absValue;

　　2.求指数

　　POWER() 函数是用来计算指数的函数。该函数接受两个参数，第一个参数为待求幂的表达式，第二个参数为幂。

SELECT POWER(5 -0.5) AS powerValue1 POWER(5 2) AS powerValue2 POWER(5 3) AS powerValue3;

　　3.求平方根

　　SQRT() 函数是用来计算平方根的函数。该函数接受一个参数，这个参数为待计算平方根的表达式。

SELECT SQRT(9) AS sqrtValue1 SQRT(3) AS sqrtValue2;

　　4.求随机数

SELECT rand(9527) randValue;

　　5.舍入到最大整数

SELECT CEILING(56.67) ceilingValue1 CEILING(-56.67) ceilingValue2;

　　6.舍入到最小整数

SELECT FLOOR(56.67) FLOORValue1 FLOOR(-56.67) floorValue2;

　　7.四舍五入

　　ROUND() 函数也是用来进行数值四舍五入的。

SELECT ROUND(56.67 0) roundValue1 ROUND(56.67 1) roundValue2 ROUND(-56.67 0) roundValue3;

　　8.求正弦值

SELECT SIN(56.67) sinValue;

　　9.求余弦值

SELECT COS(56.67) cosValue;

　　10.求反正弦值

SELECT 1 / 56.67 ASIN(1 / 56.67) asinValue;

　　11.求反余弦值

SELECT 1 / 56.67 ACOS(1 / 56.67) acosValue;

　　12.求正切值

SELECT TAN(56.67) tanValue;

　　13.求反正切值

SELECT ATAN(56.67) atanValue;

　　14.求 2 个变量的反正切

SELECT ATN2(56.67 2) atn2Value;

　　15.求余切

SELECT COT(56.67) cotValue;

　　16.求圆周率 π 值

SELECT PI() 'pi';

　　17.弧度制转换为角度制

SELECT DEGREES(56.67) degreesValue;

　　18.角度制转换为弧度制

SELECT RADIANS(56.67) radiansValue;

　　19.求符号

　　SIGN() 函数用来返回一个数值的符号，如果数值大于 0 则返回 1，如果数值等于 0 则返回 0，如果数值小于 0 则返回-1。

SELECT SIGN(1 - 3) signValue1 SIGN(3 - 1) signValue2 SIGN(0) signValue3;

　　20.求自然对数

　　LOG() 函数用来计算一个数的自然数值。

SELECT LOG(56.67) logValue;

　　21.求以 10 为底的对数

SELECT LOG10(56.67) log10Value;

二、字符串函数

　　1.计算字符串长度

SELECT LEN('abc');

　　2.字符串转换为小写

SELECT LOWER('A');

　　3.字符串转换为大写

SELECT UPPER('a');

　　4.截去字符串左侧的空格

SELECT LTRIM(' a ');

　　5.截去字符串右侧空格

SELECT RTRIM(' a ');

　　6.截去字符串两侧的空格

SELECT RTRIM(LTRIM(' a '));

　　7.取子字符串

　　SUBSTRING(string start_position length)

SELECT 'abcdef' SUBSTRING('abcdef' 2 3);

　　8.计算子字符串的位置

SELECT CHARINDEX('ly' 'Kelly') CHARINDEX('m' 'Smith');

　　9.从左侧开始取子字符串

　　LEFT(string length)

SELECT LEFT('abcdef' 3);

　　10.从右侧开始取子字符串

　　RIGHT(string length)

SELECT RIGHT('abcdef' 3);

　　11.字符串替换

　　REPLACE(string string_tobe_replace string_to_replace)

SELECT REPLACE(' abc def' ' ' '');

　　12.得到字符的 ASCII 码

　　ASCII() 函数用来得到一个字符的 ASCII 码，它有且只有一个参数，这个参数为待求 ASCII 码的字符，如果参数为一个字符串则函数返回第一个字符的 ASCII 码。

SELECT ASCII('a') ASCII('abc');

　　13.得到一个 ASCII 码数字对应的字符

　　与 ASCII() 函数正好相反，获取得到一个字符的 ASCII 码。

SELECT CHAR(56) CHAR1 CHAR(90) char2 CHAR(ASCII('a')) char3;

　　14.转换为整数

SELECT CEILING(90.021) ceilingValue;

　　15.发音匹配度

　　有的时候我们并不知道一个人姓名的准确拼写，只知道它的发音，这是在公安、医疗、教育等系统中是经常需要的功能。

SELECT SOUNDEX('jack') jackSoundexValue SOUNDEX('jeck') jeckSoundexValue SOUNDEX('joke') jockSoundexValue SOUNDEX('juke') jukeSoundexValue SOUNDEX('look') lookSoundexValue SOUNDEX('jobe') jobeSoundexValue;

　　diffERENCE() 用来简化两个字符串的发音相似度比较高，它可以计算两个字符串的发音特征值，并且比较它们，然后返回一个 0 至 4 之间的一个值来反映两个字符串的发音相似度，这个值越大则表示两个字符串发音思想度越大。

SELECT DIFFERENCE('Kerry' 'Merry') diffValue1 DIFFERENCE('Jim' 'Merry') diffValue2;

三、日期时间函数

　　1.取得当前日期时间的函数 GETDATE()

SELECT GETDATE(); SELECT CONVERT(VARCHAR(50) GETDATE() 101) AS 当前日期 CONVERT(VARCHAR(50) GETDATE() 108) AS 当前时间;

　　2.日期增减

SELECT GETDATE() DATEADD(YEAR 3 GETDATE()) AS time1 DATEADD(QUARTER 20 GETDATE()) AS time2 DATEADD(MONTH 68 GETDATE()) AS time3 DATEADD(WEEK -1000 GETDATE()) AS time4;

　　3.计算日期差额

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()) '秒';

　　4.计算一个日期是星期几

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.取得日期的指定部分

　　DATEPART() 函数的返回值是数字而 DATENAME() 函数则会将尽可能地以名称的方式作为返回值。

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()) '毫秒';

四、其他函数

　　1.类型转换

　　CAST ( expression AS data_type)

　　CONVERT ( data_type expression)

SELECT CAST('-30' AS INT) AS v1 CONVERT(DECIMAL '3.1415726') AS v2 CONVERT(DATETIME '2008-08-08 08:09:10') AS v3;

　　2.空值处理

　　COALESCE ( expression value1 value2…… valuen)

SELECT COALESCE('' GETDATE()) v1 COALESCE(NULL GETDATE()) v2 COALESCE(NULL '2008-08-08' GETDATE()) v3;

　　NULLIF() 函数

　　NULLIF ( expression1 expression2 )

DECLARE @nullIfExpression DATETIME = NULL; SELECT NULLIF('1981-03-22 00:00:00.0' GETDATE()) v1 NULLIF(GETDATE() '1981-03-22 00:00:00.0') v2 NULLIF(@nullIfExpression GETDATE()) v3 NULLIF(GETDATE() @nullIfExpression) v4 NULLIF(@nullIfExpression @nullIfExpression) v5;

　　3.CASE 函数

　　CASE expression 　　WHEN value1 THEN returnvalue1 　　WHEN value2 THEN returnvalue2 　　WHEN value3 THEN returnvalue3 　　…… 　　ELSE defaultreturnvalue 　　END

DECLARE @caseName NVARCHAR(100) = 'TONY'; SELECT (CASE @caseName WHEN 'TONY' THEN 'Tony' WHEN 'MILI' THEN 'MiLi' ELSE 'Other' END ) AS Name;

DECLARE @caseAge INT = 20; SELECT (CASE WHEN @caseAge > 60 THEN '老人' WHEN @caseAge > 30 THEN '中年' WHEN @caseAge >= 18 THEN '壮丁' ELSE '小屁孩' END ) AS '称谓';

　　4.PATINDEX() 函数

SELECT 'Tom' PATINDEX('%_m%' 'Tom') UNION SELECT 'Lily' PATINDEX('%_m%' 'Lily');

　　5.REPLICATE() 函数

SELECT 'Jim' REPLICATE('Jim' 1) UNION SELECT 'Tony' REPLICATE('Tony' 2);

　　6.REVERSE() 字符串颠倒

SELECT 'Jim' REVERSE('Jim') UNION SELECT 'Tony' REVERSE('Tony');

　　7.ISDATE() 函数

　　ISDATE() 函数用来确定输入表达式是否为有效日期。如果输入表达式是有效日期，那么 ISDATE 返回 1；否则，返回 0。

　　ISDATE ( expression )

SELECT ISDATE(NULL) AS d1 ISDATE('13/43/3425') AS d2 ISDATE('1995-10-1a') AS d3 ISDATE(19920808) AS d4 ISDATE('1/23/95') AS d5 ISDATE('1995-10-1') AS d6 ISDATE('19920808') AS d7 ISDATE(' Abc') AS d8;

　　8.ISNUMERIC() 函数

　　ISNUMERIC(expression) 函数用来确定表达式是否为有效的数值类型。如果输入表达式的计算值为有效的整数、浮点数、money 或 decimal 类型时，ISNUMERIC 返回 1；否则返回 0。

SELECT ISNUMERIC(NULL) AS d1 ISNUMERIC('13/43/3425') AS d2 ISNUMERIC('30a.8') AS d3 ISNUMERIC(19920808) AS d4 ISNUMERIC('1/23/95') AS d5 ISNUMERIC('3E-3') AS d6 ISNUMERIC('19920808') AS d7 ISNUMERIC('-30.3') AS d8;

　　9.辅助功能函数

　　APP_NAME() 函数 返回当前会话的应用程序名称；
　　CURRENT_USER 函数（注意这个函数不能带括号调用）返回当前登陆用户名；
　　HOST_NAME() 函数 返回工作站名。

SELECT APP_NAME() AppName CURRENT_USER CurrentUser HOST_NAME() HostName NEWID() UUID;