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     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 

1.ASCII(LEFT("ascs",1))
2.UPPER(china), LOWER(MACHINE)
3.LTRIM(MACHINE)+'china'+'press'
4.RTRIM(machine)+'china'+'press'
5.LTRIM(RTRIM(MACHINE))+'china'+'press'
6.REVERSE(SUBSTRING('china machine press',7,7))
7.REPLACE('I Love china!','china','BEIJING2008')
8.REPLACE('abcdabcd','c','l')
 

系统自带的函数没有做到这点，因为这些问题很不统一，如果一定要这个功能我们可以自己建立一个函数或者存储过程来修改。