数学函数
1.绝对值
S:SELECT ABS(-1) value
O:SELECT ABS (-1) value FROM dual
2.取整(大)
S:SELECT CEILING(-1.001) value
O:SELECT CEIL(-1.001) value FROM dual
3.取整(小)
S:SELECT FLOOR(-1.001) value
O:SELECT FLOOR (-1.001) value FROM dual
4.取整(截取)
S:SELECT CAST(-1.002 AS
INT) value
O:SELECT TRUNC(-1.002) value FROM dual
5.四舍五入
S:SELECT ROUND(1.23456,4) value 1.23460
O:SELECT ROUND (1.23456,4) value FROM dual 1.2346
6.e为底的幂
S:SELECT EXP(1) value 2.7182818284590451
O:SELECT EXP (1) value FROM dual 2.71828182
7.取e为底的对数
S:SELECT LOG(2.7182818284590451) value 1
O:SELECT LN(2.7182818284590451) value FROM dual; 1
8.取10为底对数
S:SELECT LOG 10(10) value 1
O:SELECT LOG (10,10) value FROM dual; 1
9.取平方
S:SELECT SQUARE(4) value 16
O:SELECT POWER(4,2) value FROM dual 16
10.取平方根
S:SELECT SQRT(4) value 2
O:SELECT SQRT(4) value FROM dual 2
11.求任意数为底的幂
S:SELECT POWER (3,4) value 81
O:SELECT POWER (3,4) value FROM dual 81
12.取随机数
S:SELECT RAND() value
O:SELECT sys.dbms_random.value(0,1) value FROM dual;
13.取符号
S:SELECT SING(-8) value -1
O:SELECT SING (-8) value FROM dual -1
数值间比较
1. 求集合最大值
S:SELECT MAX(value) value FROM
(SELECT 1 value
UNION
SELECT -2 value
UNION
SELECT 4 value
UNION
SELECT 3 value)a
O:SELECT GREATEST(1,-2,4,3) value FROM dual
2. 求集合最小值
S:SELECT MIN(value) value FROM
(SELECT 1 value
UNION
SELECT -2 value
UNION
SELECT 4 value
UNION
SELECT 3 value)a
O:SELECT LEAST(1,-2,4,3) value FROM dual
3.如何处理null值(F2中的null以10代替)
S:SELECT F1,ISNULL(F2,10) value FROM Tbl
O:SELECT F1,NVL(F2,10) value FROM Tbl
4.求字符序号
S:SELECT ASCII('a') value
O:SELECT ASCII('a') value FROM dual
5.从序号求字符
S:SELECT CHAR(97) value
O:SELECT CHR(97) value FROM dual
6.连接
S:SELECT '11'+'22'+'33' value
O:SELECT CONCAT('11','22') 33 value FROM dual
7.子串位置 --返回3
S:SELECT CHARINDEX('s','sdsq',2) value
O:SELECT INSTR('sdsq','s',2) value FROM dual
8.求子串
S:SELECT SUBSTRING('abcd',2,2) value
O:SELECT SUBSTR ('abcd',2,2) value FROM dual
9.子串代替
返回aijklmnef
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
O:SELECT REPLACE('abcdef', 'bcd', 'ijklmn') value FROM dual
10.子串全部替换
S:SELECT REPLACE('fasdbfasegas','fa','我' ) value
O:SELECT TRANSLATE('fasdbfasegas','fa','我' ) value FROM dual
11.长度
S:LEN,DATALENGTH
O: LENGTH
12.左补空格(LPAD的第一个参数为空格则同space函数)
S:SELECT space(10)+'abcd' value
O:SELECT LPAD('abcd',14) value FROM dual
13.右补空格(RPAD的第一个参数为空格则同space函数)
S:SELECT 'abcd'+space(10) value
O:SELECT RPAD('abcd',14) value FROM dual
14.删除空格
S:LTRIM,RTRIM
O: LTRIM,RTRIM,TRIM
15.发音相似性比较(这两个单词返回值一样,发音相同)
S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') FROM dual
SQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers') 比较SOUNDEX的差
返回0-4,4为同音,1最高
日期函数
1.系统时间
S:SELECT GETDATE() value
O:SELECT SYSDATE value FROM dual
2.求日期
S:SELECT CONVERT(CHAR(10), GETDATE (),111) value
O:SELECT TRUNC(SYSDATE) value FROM dual
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') value FROM dual
3.求时间
S:SELECT CONVERT (CHAR (8), GETDATE (),108) value
O:SELECT TO_CHAR(SYSDATE,'hh24:mm:ss') value FROM dual
4.取日期时间的其他部分
SATEPART 和 DATENAME 函数
(第一个参数决定)
O: TO_CHAR函数
第二个参数决定
参数---------------------------------下表需要补充
year yy, yyyy
quarter qq, q (季度)
month mm, m (m O无效)
dayofyear dy, y (O表星期)
day dd, d (d O无效)
week wk, ww (wk O无效)
weekday dw (O不清楚)
Hour hh,hh12,hh24 (hh12,hh24 S无效)
minute mi, n (n O无效)
second ss, s (s O无效)
millisecond ms (O无效)
----------------------------------------------
5.字符串转时间
S:可以直接转或者SELECT CAST('2004-09-08' AS
DATATIME) value
O:SELECT TO_DATE('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;
6.求两日期某一部分的差(比如秒)
S:SELECT DATADIFF(ss,GETDATE(),GETDATE()+12.3) value
O:直接用两个日期相减(比如d1-d2=12.3)
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
7.根据差值求新的日期(比如分钟)
S:SELECT DATEADD(mi,8,GATDATE()) value
O:SELECT SYADATE+8/60/24 vaule FROM DUAL;