Oracle 字符串函数

---- SQL> select greatest(111,222) from dual; GREATEST(111,222) -----------------       222 SQL> select least(111,222) from dual; LEAST(111,222) --------------    111 SQL> select floor(-7.8) from dual; select sys_guid() from dual; SYS_GUID() -------------------------------- 52E859DC8A9D0DA8E053FB6410AC9549 insert into t1 values('我们'); insert into t1 values('我你'); insert into t1 values('你们'); insert into t1 values('吃饭'); insert into t1 values('他'); > select * from t1 order by nlssort(name,'nls_sort=schinese_pinyin_m'); SQL> select systimestamp from dual; SQL> select vsize(ename) "byte" from emp where deptno=30; SQL> select ora_hash(ename) from emp; SQL> select extract( hour from sysdate) year from emp; SQL> select sign(3434) from dual; ------------------------------------------ regular expression regexp_count regexp_instr regexp_replace regexp_substr regexp_like regexp_like SQL> select ename from emp where regexp_like(ename,'A|S'); SQL> select ename from emp where ename like '%A%' or ename like '%S%'; SQL> select ename from emp where regexp_like(ename,'^A'); SQL> select ename from emp where regexp_like(ename,'G$'); SQL> select ename from emp where regexp_like(ename,'^[A-z]+$'); SQL> select ename from emp where regexp_like(ename,'s','c'); no rows selected SQL> select ename from emp where regexp_like(ename,'S','i'); regexp_replace selelect replace('abc','a','d')   select regexp_replace('abcabc','a','0',1,2,'i') from dual; regexp_instr SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78)0)', 1, 1, 1, 'i',1)  "REGEXP_INSTR" FROM DUAL; 0123(((abc)(de)f)ghi)45(678) abcdefghi  abcdef  abc  de 678 ((河北省)(邢台)(沙河))               河北省邢台啊啊 河北省邢台版本 河北生石家庄 regexp_substr select susbtr('172.16.50.1',) 172.16.100.1 172.168.9. 172.16.100.2 SELECT   REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',                 ',[^,]+,') "REGEXPR_SUBSTR"   FROM DUAL; ^[,] [^,] SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL; DECODE CASE  10          +1000 20          +1500 30           x 40          -500 sign sal  <2000  2000-4350  1% 4350+       2% select  decode  sal,sal<2000,sal,    sal>=2000 and sal<4000, sal*0.99 case  select  case   when sal<2000 then sal        when between 2000 and 4000  then sal*0.99        else  sal*0.98 end   sal from  emp;   select  case   when deptno =10  then sal+1000        when deptno=20  then sal+2000        else  sal end   sal, case   deptno  when 10  then sal+1000                when 20  then sal+2000        else  sal end   sal, from  emp;   ------------------------------------------------ 最大值  最小值  平均值    求和  统计 max     min    avg      sum  count() 字符 数字 日期 --------------------------------------------------- 分组函数 select ...      group by    (1)  聚集函数 (2) 加到group by后面 select  name, sum(case subject when '语文' then grade else 0 end) "语文",  max(case when subject='英语'  then grade else 0 end ) "英语",  sum(decode(subject,'数学',grade,0)) "数学"  from  t4 group  by  name; select '我们' from dual;