Loading

Friday, October 28, 2011

ORACLE SQL TUTORIAL: STRING FUNCTIONS


STRING FUNCTIONS

Ø  Initcap
Ø  Upper
Ø  Lower
Ø  Length
Ø  Rpad
Ø  Lpad
Ø  Ltrim
Ø  Rtrim
Ø  Trim
Ø  Translate
Ø  Replace
Ø  Soundex
Ø  Concat  ( ‘ || ‘ Concatenation operator)
Ø  Ascii
Ø  Chr
Ø  Substr
Ø  Instr
Ø  Decode
Ø  Greatest
Ø  Least
Ø  Coalesce
a) INITCAP

     This will capitalize the initial letter of the string.

     Syntax: initcap (string)

     Ex:
          SQL> select initcap('computer') from dual;

INITCAP
-----------
Computer

b) UPPER

     This will convert the string into uppercase.

     Syntax: upper (string)

     Ex:
          SQL> select upper('computer') from dual;
UPPER
-----------
COMPUTER

c) LOWER

     This will convert the string into lowercase.

     Syntax: lower (string)

     Ex:
          SQL> select lower('COMPUTER') from dual;



LOWER
-----------
computer

d) LENGTH

     This will give length of the string.

     Syntax: length (string)

     Ex:
          SQL> select length('computer') from dual;

LENGTH
-----------
       8

e) RPAD

     This will allows you to pad the right side of a column with any set of characters.

     Syntax: rpad (string, length [, padding_char])

     Ex:
          SQL> select rpad('computer',15,'*'), rpad('computer',15,'*#') from dual;

RPAD('COMPUTER'  RPAD('COMPUTER'
----------------------  ----------------------
computer*******    computer*#*#*#*

-- Default padding character was blank space.

f) LPAD

     This will allows you to pad the left side of a column with any set of characters.
     Syntax: lpad (string, length [, padding_char])

     Ex:
          SQL> select lpad('computer',15,'*'), lpad('computer',15,'*#') from dual;

LPAD('COMPUTER'  LPAD('COMPUTER'
---------------------   ---------------------
*******computer   *#*#*#*computer

-- Default padding character was blank space.

g) LTRIM

     This will trim off unwanted characters from the left end of string.

     Syntax: ltrim (string  [,unwanted_chars])

     Ex:
          SQL> select ltrim('computer','co'), ltrim('computer','com') from dual;

LTRIM(  LTRIM
--------  ---------
mputer   puter

SQL> select ltrim('computer','puter'), ltrim('computer','omputer') from dual;

LTRIM('C  LTRIM('C
----------   ----------
computer   computer
          
           -- If you haven’t specify any unwanted characters it will display entire string.

h) RTRIM

     This will trim off unwanted characters from the right end of string.

     Syntax: rtrim (string [, unwanted_chars])

     Ex:
          SQL> select rtrim('computer','er'), rtrim('computer','ter') from dual;
RTRIM(  RTRIM
--------  ---------
comput   compu

SQL> select rtrim('computer','comput’), rtrim('computer','compute') from dual;

RTRIM('C  RTRIM('C
----------   ----------
computer   computer
           -- If you haven’t specify any unwanted characters it will display entire string.

i) TRIM

     This will trim off unwanted characters from the both sides of string.

     Syntax: trim (unwanted_chars from string)

     Ex:
          SQL> select trim( 'i' from 'indiani') from dual;

TRIM(
-----
ndian
         
SQL> select trim( leading'i' from 'indiani') from dual;        -- this will work as LTRIM

TRIM(L
------
ndiani


SQL> select trim( trailing'i' from 'indiani') from dual;         -- this will work as RTRIM

TRIM(T
------
Indian

j) TRANSLATE

     This will replace the set of characters, character by character.

     Syntax: translate (string, old_chars, new_chars)

     Ex:
          SQL> select translate('india','in','xy') from dual;

TRANS
--------
xydxa

k) REPLACE

     This will replace the set of characters, string by string.

     Syntax: replace (string, old_chars [, new_chars])

     Ex:
          SQL> select replace('india','in','xy'), replace(‘india’,’in’) from dual;

REPLACE   REPLACE
-----------  -----------
Xydia         dia

l) SOUNDEX

    This will be used to find words that sound like other words, exclusively used in where clause.
    Syntax: soundex (string)

    Ex:
         SQL> select * from emp where soundex(ename) = soundex('SMIT');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL     DEPTNO
     --------  --------      -----             -----  ------------      --------- ----------
      7369    SMITH      CLERK         7902   17-DEC-80        500         20

m) CONCAT

    This will be used to combine two strings only.

    Syntax: concat (string1, string2)

    Ex:
         SQL> select concat('computer',' operator') from dual;

CONCAT('COMPUTER'
-------------------------
computer operator

    If you want to combine more than two strings you have to use concatenation operator (||).

         SQL> select 'how' || ' are' || ' you' from dual;

'HOW'||'ARE
---------------
how are you

n) ASCII

    This will return the decimal representation in the database character set of the first
     character of the string.

    Syntax: ascii (string)

    Ex:
         SQL> select ascii('a'), ascii('apple') from dual;

ASCII('A')  ASCII('APPLE')
------------  ------------------
        97             97

o) CHR

    This will return the character having the binary equivalent to the string in either the
    database character set or the national character set.

    Syntax: chr (number)

    Ex:
         SQL> select chr(97) from dual;

CHR
-----
   a

p) SUBSTR

     This will be used to extract substrings.

     Syntax: substr (string, start_chr_count [, no_of_chars])

     Ex:
SQL> select substr('computer',2), substr('computer',2,5), substr('computer',3,7) from
         dual;

SUBSTR(  SUBST  SUBSTR
----------  -------   --------
omputer  omput   mputer
Ø  If no_of_chars parameter is negative then it will display nothing.
Ø  If both parameters except string are null or zeros then it will display nothing.
Ø  If no_of_chars parameter is greater than the length of the string then it ignores and calculates based on the orginal string length.
Ø  If start_chr_count is negative then it will extract the substring from right end.

1              2              3              4              5              6              7              8

C             O             M            P             U             T              E              R

                               -8             -7            -6            -5            -4            -3            -2            -1

q) INSTR

     This will allows you for searching through a string for set of characters.

     Syntax: instr (string, search_str [, start_chr_count [, occurrence] ])

     Ex:
          SQL> select instr('information','o',4,1), instr('information','o',4,2) from dual;

INSTR('INFORMATION','O',4,1) INSTR('INFORMATION','O',4,2)
------------------------------------  -------------------------------------
                           4                                                   10

Ø  If you are not specifying start_chr_count and occurrence then it will start search from
      the beginning and finds first occurrence only.
Ø  If both parameters start_chr_count and occurrence are null, it will display nothing.

r) DECODE

    Decode will act as value by value substitution.
    For every value of field, it will checks for a match in a series of if/then tests.

    Syntax: decode (value, if1, then1, if2, then2, ……. else);

    Ex:
          SQL> select sal, decode(sal,500,'Low',5000,'High','Medium') from emp;

       SAL     DECODE
                                                 -----    ---------
       500  Low
      2500 Medium
      2000                 Medium
      3500                 Medium
      3000                 Medium
      5000                 High
      4000                 Medium
      5000                 High
      1800                 Medium
      1200                 Medium
      2000                 Medium
      2700                 Medium
      2200                 Medium
      3200                 Medium 

SQL> select decode(1,1,3), decode(1,2,3,4,4,6) from dual;


DECODE(1,1,3) DECODE(1,2,3,4,4,6)
-----------------  ------------------------
            3                                  6

Ø  If the number of parameters are odd and different then decode will display nothing.
Ø  If the number of parameters are even and different then decode will display last
      value.
Ø  If all the parameters are null then decode will display nothing.
Ø  If all the parameters are zeros then decode will display zero.

s) GREATEST

     This will give the greatest string.

     Syntax: greatest (strng1, string2, string3 … stringn)   

     Ex:
           SQL> select greatest('a', 'b', 'c'), greatest('satish','srinu','saketh') from dual;


GREAT GREAT
-------  -------
    c             srinu

Ø  If all the parameters are nulls then it will display nothing.
Ø  If any of the parameters is null it will display nothing.

t) LEAST

    This will give the least string.

    Syntax: greatest (strng1, string2, string3 … stringn)    

    Ex:
           SQL> select least('a', 'b', 'c'), least('satish','srinu','saketh') from dual;


LEAST LEAST
-------  -------
    a             saketh

Ø  If all the parameters are nulls then it will display nothing.
Ø  If any of the parameters is null it will display nothing.

u) COALESCE

    This will gives the first non-null string.

    Syntax: coalesce (strng1, string2, string3 … stringn)    

    Ex:
         SQL> select coalesce('a','b','c'), coalesce(null,'a',null,'b') from dual;

COALESCE COALESCE
-----------   -----------
       a                          a








1 Responses to “ORACLE SQL TUTORIAL: STRING FUNCTIONS”

Sridevi K said...
October 8, 2016 at 1:02 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.