13. STRING FUNCTIONS

STRING FUNCTIONS

•  LENGTH
•  VSIZE
•  DUMP
•  REVERSE
•  SOUNDEX
•  UPPER
•  LOWER
•  INITCAP
•  LTRIM
•  RTRIM
•  LPAD
•  RPAD
•  TRANSLATE
•  REPLACE
•  DECODE
•  SUBSTR
•  INSTR
•  SUBSTRB
•  SUBSTRC
•  SUBSTR2
•  SUBSTR4
•  CONCAT

FROM 10G ON WARDS THEY INTRODUCED REGUAL EXPRESSIONS  

• REGEXP_LIKE
•  REGEXP_COUNT(11G)
•  REGEXP_SUBSTR
•  REGEXP_SUBSTR
•  REGEXP_REPLACE

 LENGTH: TO FIND OUT THE NO OF CHARACTERS IN A GIVEN STRING

                      EG: SELECT LENGTH (‘ABC’) FROM DUAL;

           VSIZE: NO OF BYTES ACCOUPIED BY A GIVEN STRING    

                    EG: SELECT LENGTH (‘ABC’), VSIZE (‘ABC’) FROM DUAL;

    EX: SELECT LENGTH(SYSDATE), VSIZE(SYSDATE) FROM DUAL; (DIFFERS);

           DUMP: TO DISPLAY THE GIVEN CHARACTER IN DATABASE                                           CHARACTER SET

                     EG: SELECT DUMP (‘ABC’) FROM DUAL;

       REVERSE: TO DISPLAY THE GIVEN STRING IN REVERSE MANNER;

                     
                    EG: SELECT REVERSE (‘ABC’) FROM DUAL;

                          
                          SELECT REVERSE (ENAME) FROM DUAL;

         SOUNDEX: WORKS BASE ON PHONITICAL SOUNDS.  INTERNALLY IT                                   CONVERTS THE STRING INTO CODE VALUES.

EG: SELECT * FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX(‘SMITHE’);

      SELECT SOUNDEX (ENAME) FROM EMP;

            CONCAT(||): TO APPEND THE STRINGS;

                  SYN: CONCAT (S1,S2);

                    EG: SELECT CONCAT (‘WEL’,’COME’) FROM DUAL;

                          SELECT ‘WEL’||’COME’ FROM DUAL;

          DISADV: concat join only two strings unlike concatenation operators

     NOTE: both will ignore the null values

       CERT:   SELECT ‘WEL’||NULL||’COME’ FROM DUAL;

                        SELECT CONCAT(‘WEL’,NULL) FROM DUAL;

                        SELECT CONCAT(NULL,’WELL’) FROM DUAL;

        UPPER: To convert the string into upper case or capitalize

        
         LOWER: Reverse to that of upper

            EG: SELECT UPPER (‘abs’), LOWER (‘ABC’) FROM DUAL;

               INITCAP:  CONVERTS THE INITIAL LETTER INTO UPPER CASE  AND                                   REMAINING LETTERS INTO LOWER CASE

           CERT: SEECT INITCAP(‘abc’) ,INITCAP(‘abc  abc’),                                                     INITCAP(‘abc_efg%hij’) FROM DUAL;

       LTRIM AND RTRIM: TO TRIM THE CHARACTERS FROM LEFT OR FROM                                             RIGHT ENDS

         TRIM: TO TRIM FROM BOTH ENDS.

         SYN: TRIM([LEADING|BOTH|TRAILING  [‘C’ FROM]],’TEXT’);

        NOTE: DEFAULT TRIMS SPACES;

EG:  SELECT LTRIM(‘WELCOME’,’W’) FROM DUAL;

       SELECT LTRIM(‘WELCOME,’E’) FROM DUAL;

       SELECT RTRIM(‘WELCOMEEE’,’E’) FROM DUAL;

       SELECT LTRIM(‘EEEWELCOMEE’,’E’) FROM DUAL;

       SELECT LTRIM(‘EEEWELCOME’,’WE’) FROM DUAL;

       SELECT LTRIM(RTRIM(‘EWEWELCOME’,’EW’),’WE’) FROM DUAL;

       SELECT TRIM(‘E’ FROM ‘EEEWELCOMEEE’) FROM DUAL;

       SELECT TRIM(‘    WELCOME    ‘) FROM DUAL;

       SELECT TRIM(LEADING ‘E’ FROM ‘EEEWELCOME’) FROM DUAL;

  LPAD AND  RPAD: To append the character from left or from right end of a                                     given string to a given position

SYN:  LPAD(S,N,’C’);

                   
                  RPAD(S,N,’C’);

          EG: SELECT LPAD(‘WELCOME’,10,’*’) FROM DUAL;

                SELECT RPAD(‘WELCOME’,9,’*@’) FROM DUAL;

                SELECT RPAD(‘WELCOME’,10,’*@’) FROM DUAL;

                SELECT LPAD(‘WELCOME’,7,’*’) FROM DUAL;

                SELECT LPAD(‘WELCOME’,6,’*’) FROM DUAL;

                SELECT RPAD(‘WELCOME’,6,’*’) FROM DUAL;

    TRANSLATE: To translate character wise

SYN: TRANSLATE(S,’C’,’C’) : It takes strings only individually

EX:  SELECT TRANSLATE(‘WELCOME’,’E’,’A’) FROM DUAL;

       SELECT TRANSLATE(‘WELCOME’, ‘EL’,’A’) FROM DUAL;

       SELECT TRANSLATE(‘WELCOME ‘EL’,’AB’) FROM DUAL;

       SELECT TRANSLATE(‘WELCOME’,’EL’,’A_’) FROM DUAL;

DISADV: NOT POSSIBLE TO REPLACE STRING WISE

   REPLACE: To replace string wise

        SYN: REPLACE(S,S,S);

EG: SELECT TRANSLATE(‘INDIA’,’IN’,’XY’),REPLACE(‘INDIA’,’IN’,’XY’) FROM               DUAL;

      SELECT JOB,REPLACE(JOB,’MANAGER’,’MGR’) FROM EMP;

DISADV:  NOT POSSIBLE TO REPLACE MORE THAN ONE STRING

  DECODE: (ORACLE) It works as same to that of  ‘IF CONDITION’ IN SQL;

SYN: DECODE (COLUMN|EXP|VALUE|NULL, COND1, DO1, COND2, DO2, …[ELSE]);

NOTE:  It considers the null values.  In decode nulls are equal     
   

    EX: MAX 255 ARGUMENTS ARE ALLOWED;

          MIN  3 ARGUMENTS MANDATOREY;

    EG: SELECT  DECODE(1,2,3) FROM DUAL;

          SELECT DECODE(1,2,3,1,2,3) FROM DUAL;

          SELECT DECODE(NULL,1,NULL,2) FROM DUAL;

          SELECT DECODE(1,DECODE(1,2,3),2,NULL) FROM DUAL;

          SELECT  DECODE(JOB,’MANAGER’,’MGR’,’SALESMAN’,’SLS’,JOB)                        FROM DUAL;

DISADV: Won’t allow relational operator

   SUBSTR: TO DISPLAY THE SET OF CHARACTERS FROM A GIVEN POSITION

       SYN: SUBSTR(S,M,(N));

       
              S = STRING,

              M = POSITION,

              N = NO OF CHARACTERS

EG: SELECT SUBSTR(‘WELCOME’,2,2) FROM DUAL;

      SELECT SUBSTR(‘WELCOME’,1,2) FROM DUAL;

      SELECT SUBSTR(‘WELCOME’,7,2) FROM DUAL;

      SELECT SUBSTR(‘WELCOME’,2,-2) FROM DUAL;

      SELECT SUBSTR(‘WELCOME’,2) FROM DUAL;

      SELECT SUBSTR(‘WELCOME’,-2,2) FROM DUAL;

      SELECT SUBSTR(‘WELCOME’,-8,2) FROM DUAL;

      SELECT SUBSTR(‘WELCOME,1) FROM DUAL;

      SELECT SUBSTR(‘WELCOME’,0) FROM DUAL;

  INSTR:
           SYN: INSTR (S,’C’,[P [,O]]);

                S = STRING

                C = CHARACTER

                P = POSITION

                O = OCCURANCE

EG: SELECT INSTR(‘WELCOME’,’E’,1,1) FROM DUAL;

      SELECT INSTR(‘WELCOME’,’E’,1,2) FROM DUAL;

      SELECT INSTR(‘WELCOME’,’E’,2,1) FROM DUAL;

      SELECT INSTR(‘WELCOME’,’E’,3,2) FROM DUAL;

      SELECT INSTR(‘WELCOME’,’E’,3,-1) FROM DUAL;

      SELECT INSTR(‘WELCOME’,’E’) FROM DUAL;

      SELECT INSTR(‘WELCOME’,’E’,1) FROM DUAL;

      SELECT INSTR(‘WELCOME’,’E’,-3,1) FROM DUAL;