sql server
替换null:isnull(arg,value)
如:select isnull(salary,0.0) from emp 如果salary为null的话,用0.0替换
与null比较: is not null,is null
如 select * from emp where salary is null salary 等于null
如: select * from emp where salary is not null salary 不等于null
--------------------------------------------------------------------------------------
Oracle
替换null: nvl(arg,value)
如: select nvl(salary,0.0) form orders
与null比较: is not null,is null
如 select * from emp where salary is null salary 等于null
如: select * from emp where salary is not null salary 不等于null
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------SQL Server:
1.CAST函数
函数语法如下
CAST (表达式 AS 数据类型)
举例: select cast(emp_id AS varchar(20)) from emp
select cast(age AS int) from emp
SQLServer 常用数据类型:
·Char(n)
·Varchar(n)
·Nchar(n)
·Nvarchar(n)
·Datetime
·Decimal(p[,s])
·Float(n)
·Int
·Bit
·Text
·Ntext
--------------------------------------------------------------------------------------
Oracle
日期转字符
TO_CHAR(日期,'格式'):
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
注:sysdate为oracle系统当前时间,类似sql server的getDate();
字符转日期
TO_DATE('2009-08-18','YYYY-MM-DD')
数字转为字符串用to_char函数,例如
to_char(1210.73)就得到'1210.73'
两个函数都可以设定转换的格式,例如
to_char(1210.73, '$9,999.00')就得到'$1,210.73'
字符串转为数字用to_number函数
TO_NUMBER(表达式)
to_number(salary)
例如
to_number('1210.73')就得到1210.73