首页 > 代码库 > SQL_字符操作函数

SQL_字符操作函数

原创作品。出自 “深蓝的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     SMITHCLERKALLEN      DBA       ALLENDBAWARD       DBA       WARDDBAJONES      MANAGER   JONESMANAGERMARTIN     DBA       MARTINDBABLAKE      DBA       BLAKEDBACLARK      MANAGER   CLARKMANAGERSCOTT      ANALYST   SCOTTANALYSTKING       PRESIDENT KINGPRESIDENTTURNER     DBA       TURNERDBAADAMS      CLERK     ADAMSCLERKJAMES      DBA       JAMESDBAFORD       ANALYST   FORDANALYSTMILLER     CLERK     MILLERCLERK14 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 CLERKALLEN      DBA       ALLENs job is DBAWARD       DBA       WARDs job is DBAJONES      MANAGER   JONESs job is MANAGERMARTIN     DBA       MARTINs job is DBABLAKE      DBA       BLAKEs job is DBACLARK      MANAGER   CLARKs job is MANAGERSCOTT      ANALYST   SCOTTs job is ANALYSTKING       PRESIDENT KINGs job is PRESIDENTTURNER     DBA       TURNERs job is DBAADAMS      CLERK     ADAMSs job is CLERKJAMES      DBA       JAMESs job is DBAFORD       ANALYST   FORDs job is ANALYSTMILLER     CLERK     MILLERs job is CLERK14 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                0FORD       FORDANALYST                     4                0 
  

length函数 

SQL> select ename,length(ename) from emp;ENAME      LENGTH(ENAME)---------- -------------SMITH                  5ALLEN                  5WARD                   4JONES                  5MARTIN                 6BLAKE                  5CLARK                  5SCOTT                  5KING                   4TURNER                 6ADAMS                  5JAMES                  5FORD                   4MILLER                 614 rows selected 
 

instr函数 

SQL> select ename,instr(ename,'A') from emp ;ENAME      INSTR(ENAME,'A')---------- ----------------SMITH                     0ALLEN                     1WARD                      2JONES                     0MARTIN                    2BLAKE                     3CLARK                     3SCOTT                     0KING                      0TURNER                    0ADAMS                     1JAMES                     2FORD                      0MILLER                    014 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                0ALLEN      ALLENDBA                        5                0WARD       WARDDBA                         4                0JONES      JONESMANAGER                    5                0MARTIN     MARTINDBA                       6                0BLAKE      BLAKEDBA                        5                0CLARK      CLARKMANAGER                    5                0SCOTT      SCOTTANALYST                    5                0KING       KINGPRESIDENT                   4                0TURNER     TURNERDBA                       6                0ADAMS      ADAMSCLERK                      5                0JAMES      JAMESDBA                        5                0FORD       FORDANALYST                     4                0MILLER     MILLERCLERK                     6                014 rows selected 
 

lpad函数

SQL> select ename,lpad(ename,10,'-') from emp;ENAME      LPAD(ENAME,10,'-')---------- --------------------SMITH      -----SMITHALLEN      -----ALLENWARD       ------WARDJONES      -----JONESMARTIN     ----MARTINBLAKE      -----BLAKECLARK      -----CLARKSCOTT      -----SCOTTKING       ------KINGTURNER     ----TURNERADAMS      -----ADAMSJAMES      -----JAMESFORD       ------FORDMILLER     ----MILLER14 rows selected 
 

trim函数

SQL> select trim('s' from 'sdfde') from dual;TRIM('S'FROM'SDFDE')--------------------dfdeSQL> select trim('s' from 'sdsfsde') from dual;TRIM('S'FROM'SDSFSDE')----------------------dsfsdeSQL> select trim('s' from 'ssdsfsde') from dual;TRIM('S'FROM'SSDSFSDE')-----------------------dsfsde

 

原创作品。出自 “深蓝的blog” 博客。欢迎转载,转载时请务必注明下面出处,否则追究版权法律责任。

深蓝的blog:http://blog.csdn.net/huangyanlong/article/details/40213181 

 

SQL_字符操作函数