Fundamentals of SQL - Basics with example - 7

     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)