Sql_ Character manipulation functions

Source: Internet
Author: User

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the following sources, otherwise, the legal responsibility to pursue copyright.

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

Mind Mapping:

A simple example demonstrates the use of a character manipulation function using the simplest of operations, as listed below:

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, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the following sources, otherwise, the legal responsibility to pursue copyright.

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

Sql_ character manipulation functions

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.