原创作品。出自 “深蓝的blog” 博客。欢迎转载，转载时请务必注明下面出处，否则追究版权法律责任。
深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/40213181
思维导图：
用简单的样例演示，例如以下列举。用最简单的操作体会字符操作函数的使用方法：
concat函数
SQL> select ename,job,concat(ename,job) from emp;
ENAME JOB CONCAT(ENAME,JOB)
---------- --------- -------------------
SMITH CLERK SMITHCLERK
ALLEN DBA ALLENDBA
WARD DBA WARDDBA
JONES MANAGER JONESMANAGER
MARTIN DBA MARTINDBA
BLAKE DBA BLAKEDBA
CLARK MANAGER CLARKMANAGER
SCOTT ANALYST SCOTTANALYST
KING PRESIDENT KINGPRESIDENT
TURNER DBA TURNERDBA
ADAMS CLERK ADAMSCLERK
JAMES DBA JAMESDBA
FORD ANALYST FORDANALYST
MILLER CLERK MILLERCLERK
14 rows selected
SQL> select ename,job,concat(ename,'s job is ' || job) from emp;
ENAME JOB CONCAT(ENAME,'SJOBIS'||JOB)
---------- --------- ----------------------------
SMITH CLERK SMITHs job is CLERK
ALLEN DBA ALLENs job is DBA
WARD DBA WARDs job is DBA
JONES MANAGER JONESs job is MANAGER
MARTIN DBA MARTINs job is DBA
BLAKE DBA BLAKEs job is DBA
CLARK MANAGER CLARKs job is MANAGER
SCOTT ANALYST SCOTTs job is ANALYST
KING PRESIDENT KINGs job is PRESIDENT
TURNER DBA TURNERs job is DBA
ADAMS CLERK ADAMSs job is CLERK
JAMES DBA JAMESs job is DBA
FORD ANALYST FORDs job is ANALYST
MILLER CLERK MILLERs job is CLERK
14 rows selected
substr函数
SQL> select ename,concat(ename,job),length(ename),instr(ename,'a') from emp where substr(job,1,5)='ANALY';
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')
---------- ------------------- ------------- ----------------
SCOTT SCOTTANALYST 5 0
FORD FORDANALYST 4 0
length函数
SQL> select ename,length(ename) from emp;
ENAME LENGTH(ENAME)
---------- -------------
SMITH 5
ALLEN 5
WARD 4
JONES 5
MARTIN 6
BLAKE 5
CLARK 5
SCOTT 5
KING 4
TURNER 6
ADAMS 5
JAMES 5
FORD 4
MILLER 6
14 rows selected
instr函数
SQL> select ename,instr(ename,'A') from emp ;
ENAME INSTR(ENAME,'A')
---------- ----------------
SMITH 0
ALLEN 1
WARD 2
JONES 0
MARTIN 2
BLAKE 3
CLARK 3
SCOTT 0
KING 0
TURNER 0
ADAMS 1
JAMES 2
FORD 0
MILLER 0
14 rows selected
SQL> select ename,concat(ename,job),length(ename),instr(ename,'a') from emp;
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')
---------- ------------------- ------------- ----------------
SMITH SMITHCLERK 5 0
ALLEN ALLENDBA 5 0
WARD WARDDBA 4 0
JONES JONESMANAGER 5 0
MARTIN MARTINDBA 6 0
BLAKE BLAKEDBA 5 0
CLARK CLARKMANAGER 5 0
SCOTT SCOTTANALYST 5 0
KING KINGPRESIDENT 4 0
TURNER TURNERDBA 6 0
ADAMS ADAMSCLERK 5 0
JAMES JAMESDBA 5 0
FORD FORDANALYST 4 0
MILLER MILLERCLERK 6 0
14 rows selected
lpad函数
SQL> select ename,lpad(ename,10,'-') from emp;
ENAME LPAD(ENAME,10,'-')
---------- --------------------
SMITH -----SMITH
ALLEN -----ALLEN
WARD ------WARD
JONES -----JONES
MARTIN ----MARTIN
BLAKE -----BLAKE
CLARK -----CLARK
SCOTT -----SCOTT
KING ------KING
TURNER ----TURNER
ADAMS -----ADAMS
JAMES -----JAMES
FORD ------FORD
MILLER ----MILLER
14 rows selected
trim函数
SQL> select trim('s' from 'sdfde') from dual;
TRIM('S'FROM'SDFDE')
--------------------
dfde
SQL> select trim('s' from 'sdsfsde') from dual;
TRIM('S'FROM'SDSFSDE')
----------------------
dsfsde
SQL> select trim('s' from 'ssdsfsde') from dual;
TRIM('S'FROM'SSDSFSDE')
-----------------------
dsfsde
原创作品。出自 “深蓝的blog” 博客。欢迎转载，转载时请务必注明下面出处，否则追究版权法律责任。
深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/40213181