oracle之sql（二）

大小写

upper所有大写 A-Z
lower所有小写 a-z
initcap 开头大写，其余小写


SQL> select ename from emp where deptno=10;


ENAME
----------
CLARK
KING
MILLER


SQL> select lower(ename) from emp where deptno=10;


LOWER(ENAM
----------
clark
king
miller


SQL> select initcap(ename) from emp where deptno=10;


INITCAP(EN
----------
Clark
King
Miller


SQL> 


SQL> select initcap('tiger_scott') from dual;


INITCAP('TI
-----------
Tiger_Scott


SQL> select initcap('tiger8scott') from dual;


INITCAP('TI
-----------
Tiger8scott


SQL> 



 

字符函数concat

SQL> select concat(ename,' is work'),job from emp;


CONCAT(ENAME,'ISWO JOB
------------------ ---------
SMITH is work   CLERK
ALLEN is work   SALESMAN
WARD is work   SALESMAN
JONES is work   MANAGER
MARTIN is work   SALESMAN
BLAKE is work   MANAGER
CLARK is work   MANAGER
SCOTT is work   ANALYST
KING is work   PRESIDENT
TURNER is work   SALESMAN
ADAMS is work   CLERK


CONCAT(ENAME,'ISWO JOB
------------------ ---------
JAMES is work   CLERK
FORD is work   ANALYST
MILLER is work   CLERK


14 rows selected.


SQL> 

 

切割

substr（expr，m，n）#m：从哪开始+|- num，n：取多少个n>0
substr('mygodssr',1,1)-->m
substr('mygodssr',-1,1)-->r


SQL> select ename from emp where substr(ename,2,1)='A';


ENAME
----------
WARD
MARTIN
JAMES


SQL> 



 

长度

SQL> select length(ename),ename from emp;#返回字符长度


LENGTH(ENAME) ENAME
------------- ----------
   5 SMITH
   5 ALLEN
   4 WARD
   5 JONES
   6 MARTIN
   5 BLAKE
   5 CLARK
   5 SCOTT
   4 KING
   6 TURNER
   5 ADAMS


LENGTH(ENAME) ENAME
------------- ----------
   5 JAMES
   4 FORD
   6 MILLER


14 rows selected.


SQL> 
SQL> insert into emp(empno,ename) values(1,'数');


1 row created.


SQL> select ename,length(ename),lengthc(ename),lengthb(ename) from emp where empno=1;


ENAME   LENGTH(ENAME) LENGTHC(ENAME) LENGTHB(ENAME) #3个字节存一个汉字--》2个字符
---------- ------------- -------------- --------------
???       3      3     9


SQL> 



 

查找

SQL> select instr(ename,'A'),ename from emp;


INSTR(ENAME,'A') ENAME
---------------- ----------
      0 SMITH  没找到
      1 ALLEN  第一个
      2 WARD   第二个
      0 JONES
      2 MARTIN
      3 BLAKE  第三个
      1 ADAMS  多个A只找第一个


SQL> select ename from emp where instr(substr(ename,1,2),'A')=1;


ENAME
----------
ALLEN
ADAMS


SQL> 



 

右|左对齐

SQL> select deptno,dname,loc from dept;


    DEPTNO DNAME  LOC
---------- -------------- -------------
10 ACCOUNTING  NEW YORK
20 RESEARCH  DALLAS
30 SALES  CHICAGO
40 OPERATIONS  BOSTON




SQL> select rpad(deptno,10,' ')deptno,dname,loc from dept;


DEPTNO DNAME LOC
---------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


SQL> 


SQL> select lpad(deptno,8,' ')deptno,dname,loc from dept;


DEPTNO DNAME LOC
-------------------------------- -------------- -------------
      10 ACCOUNTING NEW YORK
      20 RESEARCH DALLAS
      30 SALES CHICAGO
      40 OPERATIONS BOSTON


SQL> 






SQL> col dname just right; #使dname列靠右
SQL> select deptno,dname,loc from dept;


    DEPTNO    DNAME LOC
---------- -------------- -------------
10 ACCOUNTING  NEW YORK
20 RESEARCH  DALLAS
30 SALES  CHICAGO
40 OPERATIONS  BOSTON


SQL> 



 

切除

SQL> select trim('a' from 'aaabbbbaaa') from dual; 


TRIM
----
bbbb


SQL> select trim(trailing 'a' from 'aaabbbbaaa') from dual; 


TRIM(TR
-------
aaabbbb


SQL> select trim(leading 'a' from 'aaabbbbaaa') from dual; 


TRIM(LE
-------
bbbbaaa


SQL> 




SQL> select ename,trim('A' from ename) from emp;


ENAME   TRIM('A'FR
---------- ----------
SMITH   SMITH
ALLEN   LLEN



 

替换

replace（expr，old，new）


SQL> select replace('ssr and ssy','ss','mm') from dual;


REPLACE('SS
-----------
mmr and mmy


SQL>