Oracle和SQL Server的常用函数对比
1.数学函数
①绝对值
S:select abs(-1) value
O:select abs(-1) value from dual
②取整(大)
S:select ceiling(-001) value
O:select ceil(-001) value from dual
③取整(小)
S:select floor(-001) value
O:select floor(-001) value from dual
④取整(截取)
S:select cast(-002 as int) value
O:select trunc(-002) value from dual
⑤四舍五入
S:select round(23456,4) value 23460
O:select round(23456,4) value from dual 2346
⑥e为底的幂
S:select Exp(1) value
O:select Exp(1) value from dual
⑦取e为底的对数
S:select log(7182818284590451) value
O:select ln(7182818284590451) value from dual;
⑧取10为底对数
S:select log10(10) value
O:select log(10,10) value from dual;
⑨取平方
S:select SQUARE(4) value
O:select power(4,2) value from dual
⑩取平方根
S:select SQRT(4) value
O:select SQRT(4) value from dual
求任意数为底的幂
S:select power(3,4) value
O:select power(3,4) value from dual
取随机数
S:select rand() value
O:select sys.dbms_random.value(0,1) value from dual;
取符号
S:select sign(-8) value -1
O:select sign(-8) value from dual -1
2.数值比较
①求集合最大值
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
②求集合最小值
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
③如何处理null值(F2字段中的null以10代替)
S:select F1,IsNull(F2,10) value from Tbl
O:select F1,nvl(F2,10) value from Tbl
3.字符串函数
①求字符序号
S:select ascii('a') value
O:select ascii('a') value from dual
②从序号求字符
S:select char(97) value
O:select chr(97) value from dual
③连接
S:select '11'+'22'+'33' value
O:select CONCAT('11','22')||33 value from dual
④子串位置--返回3
S:select CHARINDEX('s','sdsq',2) value
O:select INSTR('sdsq','s',2) value from dual
⑤模糊子串的位置--返回2,参数去掉中间%则返回7
S:select patindex('%d%q%','sdsfasdqe') value
O:oracle没发现,但是instr可以通过第四个参数控制出现次数
select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6
⑥求子串
S:select substring('abcd',2,2) value
O:select substr('abcd',2,2) value from dual
⑦子串代替--返回aijklmnef
S:SELECT STUFF('abcdef', 2, 3, 'ijklmn') value
O:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
⑧左补空格(LPAD的第1个参数为空格则同space函数)
S:select space(10)+'abcd' value
O:select LPAD('abcd',14) value from dual
⑨右补空格(RPAD的第1个参数为空格则同space函数)
S:select 'abcd'+space(10) value
O:select RPAD('abcd',14) value from dual
⑩发音相似性比较(这2个单词返回值一样,发音相同)
S:select soundex ('Smith'), soundex ('Smythe')
O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
SQL Server中用SELECT DIFFERENCE('Smithers', 'Smythers')
比较soundex的差返回0~4,4为同音,1最高
4.日期函数
①系统时间
S:select getdate() value
O:select sysdate value from dual
②求日期
S:select convert(char(10),getdate(),20) value
O:select trunc(sysdate) value from dual
select to_char(sysdate,'yyyy-mm-dd') value from dual
③求时间
S:select convert(char(8),getdate(),108) value
O:select to_char(sysdate,'hh24:mm:ss') value from dual
④当月最后一天
S:不知道
O:select LAST_DAY(sysdate) value from dual
⑤本星期的某一天(比如星期日)
S:不知道
O:SELECT Next_day(sysdate,7) vaule FROM DUAL;
⑥字符串转时间
S:select cast('2004-09-08'as datetime) value
O:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss')
vaule FROM DUAL;
⑦求2日期某一部分的差(比如秒)
S:select datediff(ss,getdate(),getdate()+3) value
O:直接用两个日期相减(比如d1-d2=3)
SELECT (d1-d2)*24*60*60 vaule FROM DUAL;
⑧根据差值求新的日期(比如分钟)
S:select dateadd(mi,8,getdate()) value
O:SELECT sysdate+8/60/24 vaule FROM DUAL;
ORACLE内部函数大全以及与SQLSERVER的区别:
下面是Oracle支持的字符函数和它们的Microsoft SQL Server等价函数。
函数 Oracle Microsoft SQL Server
把字符转换为ASCII :ASCII ASCII
字串连接: CONCAT --------------(expression + expression)
把ASCII转换为字符 CHR, CHAR
返回字符串中的开始字符(左起) INSTR ,---------------CHARINDEX
把字符转换为小写 LOWER ---------------------LOWER
把字符转换为大写 UPPER-------------------- UPPER
填充字符串的左边 LPAD --------------------N/A
清除开始的空白 LTRIM--------------------LTRIM
清除尾部的空白 RTRIM --------------------RTRIM
字符串中的起始模式(pattern) INSTR --------------------PATINDEX
多次重复字符串 RPAD --------------------REPLICATE
字符串的语音表示 SOUNDEX --------------------SOUNDEX
重复空格的字串 RPAD --------------------SPACE
从数字数据转换为字符数据 TO_CHAR --------------------STR
子串 SUBSTR --------------------SUBSTRING
替换字符 REPLACE --------------------STUFF
将字串中的每个词首字母大写 INITCAP --------------------N/A
翻译字符串 TRANSLATE --------------------N/A
字符串长度 LENGTH-------------------- DATELENGTH or LEN
列表中最大的字符串 GREATEST-------------------- N/A
列表中最小的字符串 LEAST --------------------N/A
如果为NULL则转换字串 NVL-------------------- ISNULL
日期函数
下面是Oracle支持的日期函数和它们的Microsoft SQL Server等价函数。
函数 Oracle --------------------Microsoft SQL Server
日期相加 (date column +/- value) or ADD_MONTHS --------------------DATEADD
两个日期的差 (date column +/- value) or MONTHS_BETWEEN --------------------DATEDIFF
当前日期和时间 SYSDATE --------------------GETDATE()
一个月的最后一天 LAST_DAY --------------------N/A
时区转换 NEW_TIME --------------------N/A
日期后的第一个周日 NEXT_DAY --------------------N/A
代表日期的字符串 TO_CHAR --------------------DATENAME
代表日期的整数 TO_NUMBER (TO_CHAR)) --------------------DATEPART
日期舍入 ROUND --------------------CONVERT
日期截断 TRUNC --------------------CONVERT
字符串转换为日期 TO_DATE --------------------CONVERT
如果为NULL则转换日期 NVL -------------------- ISNULL
转换函数
下面是Oracle支持的转换函数和它们的Microsoft SQL Server等价函数。
函数 Oracle --------------------Microsoft SQL Server
数字转换为字符 TO_CHAR --------------------CONVERT
字符转换为数字 TO_NUMBER --------------------CONVERT
日期转换为字符 TO_CHAR --------------------CONVERT
字符转换为日期 TO_DATE CONVERT
16进制转换为2进制 HEX_TO_RAW --------------------CONVERT
2进制转换为16进制 RAW_TO_HEX --------------------CONVERT
其它行级别的函数
下面是Oracle支持的其它行级别的函数以及它们的Microsoft SQL Server等价函数。
函数 Oracle --------------------Microsoft SQL Server
返回第一个非空表达式 DECODE -------------------------------------COALESCE
当前序列值 CURRVAL --------------------N/A
下一个序列值 NEXTVAL --------------------N/A
用户登录账号ID数字 UID --------------------SUSER_ID
用户登录名 USER --------------------SUSER_NAME
用户数据库ID数字 UID --------------------USER_ID
用户数据库名 USER --------------------USER_NAME
当前用户 CURRENT_USER -------------------- CURRENT_USER
用户环境(audit trail) USERENV --------------------N/A
在CONNECT BY子句中的级别 LEVEL --------------------N/A
合计函数
下面是Oracle支持的合计函数和它们的Microsoft SQL Server等价函数。
函数 Oracle --------------------Microsoft SQL Server
Average AVG -------------------- AVG
Count COUNT --------------------COUNT
Maximum MAX -------------------- MAX
Minimum MIN --------------------MIN
Standard deviation STDDEV --------------------STDEV or STDEVP
Summation SUM -------------------- SUM
Variance VARIANCE --------------------VAR or VARP
条件测试
Oracle的DECODE语句和Microsoft SQL Server的CASE表达式都执行条件测试。
当test_value中的值和后面的任何表达式匹配的时候,相关的值就返回。如果没有找到任何匹配的值,就返回default_value。
如果没有指定default_value,在没有匹配的时候,DECODE和CASE都返回一个NULL。下表显示了该语句的语法,
同时给出了转换DECODE命令的示例。
Oracle Microsoft SQL
DECODE (test_value,
expression1, value1
[,default_value]
)
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(DECODE(grade
,'A', 4
,'A+', 4.3
,'A-', 3.7
,'B', 3
,'B+', 3.3
,'B-', 2.7
,'C', 2
,'C+', 2.3
,'C-', 1.7
,'D', 1
,'D+', 1.3
,'D-', 0.7
,0)),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN
CASE input_expression
WHEN when_expression THEN result_expression
[ELSE else_result_expression]
END
CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(CASE grade
WHEN 'A' THEN 4
WHEN 'A+' THEN 4.3
WHEN 'A-' THEN 3.7
WHEN 'B' THEN 3
WHEN 'B+' THEN 3.3
WHEN 'B-' THEN 2.7
WHEN 'C' THEN 2
WHEN 'C+' THEN 2.3
WHEN 'C-' THEN 1.7
WHEN 'D' THEN 1
WHEN 'D+' THEN 1.3
WHEN 'D-' THEN 0.7
ELSE 0
END),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN
CASE表达式可以支持用SELECT语句执行布尔测试,这是DECODE命令所不允许的。欲了解关于CASE表达式的详细信息,
请参阅SQL Server联机手册。
把值转换为不同的数据类型
Microsoft SQL Server的CONVERT和CAST函数都是多目标转换函数。它们提供了相似的功能,
把一种数据类型的表达式转换为另一种数据类型的表达式,并且支持多种专门数据的格式。
CAST(expression AS data_type)
CONVERT (data type[(length)], expression [, style])
CAST是一个SQL-92标准的函数。这些函数执行同Oracle的TO_CHAR、TO_NUMBER、TO_DATE、HEXTORAW以及RAWTOTEXT函数相同的功能。
这里所指的数据类型是任何表达式将被转换成为的系统数据类型。不能使用用户定义的数据类型。长度参数是可选的,
该参数用于char、varchar、binary以及varbinary数据类型。允许的最大长度是8000。
转换 Oracle Microsoft SQL Server
字符到数字 TO_NUMBER(?') --------------------CONVERT(numeric, ?')
数字到字符 TO_CHAR(10) --------------------CONVERT(char, 10)
字符到日期 TO_DATE(?-JUL-97')
TO_DATE(?-JUL-1997','dd-mon-yyyy')
TO_DATE('July 4, 1997', 'Month dd, yyyy') --------------------CONVERT(datetime, ?-JUL-97')
CONVERT(datetime, ?-JUL-1997')
CONVERT(datetime, 'July 4, 1997')
日期到字符 TO_CHAR(sysdate)
TO_CHAR(sysdate, 'dd mon yyyy')
TO_CHAR(sysdate, 'mm/dd/yyyy') --------------------CONVERT(char, GETDATE())
CONVERT(char, GETDATE(), 106)
CONVERT(char, GETDATE(), 101)
16进制到2进制 HEXTORAW(?F')-------------------- CONVERT(binary, ?F')
2进制到16进制 RAWTOHEX(binary_column) --------------------CONVERT(char, binary_column)
请注意字符串是怎样转换为日期的。在Oracle中,缺省的日期格式模型是“DD-MON-YY”如果你使用任何其它格式,
你必须提供一个合适的日期格式模型。CONVERT函数自动转换标准日期格式,不需要任何格式模型。
从日期转换到字符串时,CONVERT函数的缺省输出是“dd mon yyyy hh:mm:ss:mmm(24h)”。
用一个数字风格代码来格式化输出,使它能输出为其它类型的日期格式模型。欲了解CONVERT函数的详细信息,请参阅SQL Server联机手册。
下表显示了Microsoft SQL Server日期的缺省输出。
Without Century With Century Standard Output
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default milliseconds mon dd yyyy hh:mi:ss:mmm (AM or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
用户定义函数
Oracle PL/SQL函数可以在Oracle SQL语句中使用。在Microsoft SQL Server中一般可以通过其它方式来实现同样的功能。
在SQL Server中可以用表中给出的查询来代替。
Oracle Microsoft SQL Server
SELECT SSN, FNAME, LNAME, ) TUITION_PAID,
TUITION_PAID/GET_SUM_
MAJOR(MAJOR)
AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT,
(SELECT MAJOR, SUM(TUITION_PAID) SUM_MAJOR
FROM STUDENT_ADMIN.STUDENT
GROUP BY MAJOR) SUM_STUDENT
WHERE STUDENT.MAJOR = SUM_STUDENT.MAJOR
CREATE OR REPLACE FUNCTION GET_SUM_MAJOR
(INMAJOR VARCHAR2) RETURN NUMBER
AS SUM_PAID NUMBER;
BEGIN
SELECT SUM(TUITION_PAID) INTO SUM_PAID
FROM STUDENT_ADMIN.STUDENT
WHERE MAJOR = INMAJOR;
RETURN(SUM_PAID);
END GET_SUM_MAJOR; No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax.
函數
1.轉換函數
Oracle 中有to_char(),to_date(),to_number()
Ms sql 中有cast,convert
2.系统日期:
Oracle: sysdate
Ms sql: getdate()
如
Select sysdate from dual 一定要from子句
Select getdate() 不一定要from子句
3.Decode函数相当if else,或者ms 中的case语句
语法是decode(value,if1,then1,if2,then2....)
如decode(col1,'1','true','0','fase')
4.常规函数
Nvl 语法是NVL(EXP1,EXP2) 表示如果ex1为空则返回ex2
Nvl2 语法是nvl(exp1,exp2,exp3)表示如果ex1为空,则返回ex3,否则返回ex2
Nullif 语法是nullif(ex1,ex2) 如果这两个表达式相等则返回空
coalesce语法是coalesce(ex1,ex2,....exn)返回第一个非空表达式
5.分組函數
Oracle 中的分組函數Rollup,cube
Rollup返回的结果集包含分组行和小计行,cube产生交叉报表
如:
Select a,b,sum(c) from tabname group by rollup(a,b)
Select a,b,sum(c) from tabname group by cube(a,b)
Ms sql中的分組函數 compute 和 compute by
Compute子句为行聚集函数生成汇总值,该汇总值做为一个附加的行显示
在结果集中。没有GROUP BY 子句的情况下,也可以使用关键字COMPUTE.
此关键字使用MAX,MIN,SUM,COUNT,AVG等函数生成汇总值,而compute by
则在控制中断时给出该汇总值,compute by 必须包括在order by 子句中。
還有很多函數,諸如日期函數,字符串函數等等,就不一一作比較了。
Oracle中還有很多比較好的分析函數,也不列舉了。