Sql_ Character manipulation functions

Original works. Blog from "Deep Blue blog". Welcome reprint, when reproduced please be sure to indicate the following source, or to pursue copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/40213181  

Mind Mapping:

Use a simple sample demo, such as the following list. Use the simplest operation to understand how the character manipulation functions are used:

concat function

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 are dbajones      MANAGER   joness job is managermartin     dba       MARTINs job is Dbablake      DBA       BLAKEs job was Dbaclark      MANAGER   CLARKs job is Managerscott      analyst< C17/>scotts job is analystking       President KINGs job was Presidentturner     DBA       TURNERs job is Dbaadams      Clerk     Adamss Job is clerkjames      DBA       jamess job are Dbaford       ANALYST   FORDs job is analystmiller< C27/>clerk     Millers Job is CLERK14 rows selected

  substr function  

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 function  

Sql> Select Ename,length (ename) from EMP; ename      LENGTH (ename)-----------------------SMITH                  5ALLEN                  5WARD                   4JONES                  5MARTIN                 6BLAKE                  5CLARK                  5SCOTT                  5KING                   4TURNER                 6ADAMS                  5JAMES                  5FORD                   4MILLER                 

  instr function  

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 0 WARD WARDDBA 4 0JONES jonesmanager 5 0 MARTIN MARTINDBA 6 0BLAKE blakedba 5 0 CLARK Clarkmanager 5 0SCOTT scottanalyst 5 0 KING kingpresident 4 0TURNER turnerdba 6 0 ADAMS Adamsclerk 5 0JAMES jamesdba 5 0 FORD Fordanalyst 4 0MILLER     Millerclerk 6 014 Rows selected  

  lpad function

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 function

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

Original works. Blog from "Deep Blue blog". Welcome reprint, when reproduced please be sure to indicate the following source, or to pursue copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/40213181  

Sql_ character manipulation functions