常用SQL整理2

--SQL中常用函数
1、to_char(number1):将数值和日期型变量转换成字符串变量
select to_char(1021) from INF_STUDENT_SJL;
select to_char(sysdate,'yyyy-mm-dd<->hh24:mi:ss') from INF_STUDENT_SJL;


2、to_number(string1):将字符型变量转换成数值型变量，字符串里不能有数字以外的字符
declare
  v_int integer;
  v_float number(4,2);
begin
  v_int := to_number('1021');
  v_float := to_number('10.21');
end;


3、sysdate:系统时间
select sysdate from INF_STUDENT_SJL;
select sysdate+1 from INF_STUDENT_SJL;--以一天(24h)为单位计算


4、to_date(char1):把char转换为date类型
v_date := TO_DATE('2007-12-20 18:31:34','YYYY-MM-DD')
v_date := to_date('20060912','yyyymmdd');
v_date := to_date('20060912 20:23:56','yyyymmdd hh24:mi:ss');
v_date := to_date(sysdate,'yyyymmdd hh24:mi:ss');


5、nvl(char1, replace_with):如果char1为NULL，则返回replace_with的值，否则返回string1的值
select nvl(BIRTHDAY,sysdate) from INF_STUDENT_SJL;


6、trunc(number1):向下取整
select trunc(1.97) from INF_STUDENT_SJL;--返回1


7、ceil(number1)：向上取整
select ceil(1.07) from INF_STUDENT_SJL;--返回2


8、lower(char1):把字符串全部转换为小写
select lower('aBCdef') from INF_STUDENT_SJL;


9、upper(char1):把字符串全部转换为大写
select upper('aBCdef') from INF_STUDENT_SJL;


10、length(char1):获取字符串长度函数
select length(trim('aBCdef')) from INF_STUDENT_SJL;


11、substr(char1, pos, len):截取字符串的指定部分
select substr('abcdefg',1,3) from INF_STUDENT_SJL;
select substr('abcdefg',-3,2) from INF_STUDENT_SJL;


12、concat(char1,char2):连接两个字符串
select substr('i love', 'you') from INF_STUDENT_SJL;


13、ltrim(char1):把字符串头部的空格去掉
select trim(' 123 ') from INF_STUDENT_SJL;


14、rtrim(char1):把字符串尾部的空格去掉 
select rtrim(' 123 ') from INF_STUDENT_SJL;


15、trim(char1):同时去掉所有两端的空格
select ltrim(' 123 ') from INF_STUDENT_SJL;


16、replace(原来的字符串, 要被替换掉的字符串, 要替换成的字符串)


17、instr(char1,subchar):函数返回字符串中某个指定的子串出现的开始位置，如果不存在则返回