首页 > 代码库 > 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_字符操作函数
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。