oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Character Functions available in Oracle

Character Functions

Character functions operate on values of dataype  CHAR or VARCHAR.

LOWER

Returns a given string in lower case.

select LOWER(‘SAMI’) from dual;

LOWER
-------------
sami

UPPER

Returns a given string in UPPER case.

select UPPER(‘Sami’) from dual;

UPPER
------------------
SAMI

                    

INITCAP

Returns a given string with Initial letter in capital.

select INITCAP(‘mohammed sami’) from dual;

INITCAP
------------------
Mohammed Sami

 

LENGTH

Returns the length of a given string.

select length(‘mohammed sami’) from dual;

LENGTH
------------
        13

SUBSTR

Returns a substring from a given string. Starting from position p to n characters.

For example the following query returns “sam” from the string “mohammed sami”.

select substr('mohammed sami',10,3) from dual;

Substr
--------
sam

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.

Example

The following query tests whether the character “a” occurs in string “mohammed sami”

select instr('mohammed sami','a') from dual;

INSTR
--------
4

REPLACE

Replaces a given set of characters in a string with another set of characters.

Example

The following query replaces “mohd” with “mohammed” .

select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE
---------
ali mohammed khan

INSTR

Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.

Example

The following query tests whether the character “a” occurs in string “mohammed sami”

select instr('mohammed sami','a') from dual;

INSTR
--------
4

REPLACE

Replaces a given set of characters in a string with another set of characters.

Example

The following query replaces “mohd” with “mohammed” .

select replace('ali mohd khan','mohd','mohammed') from dual;

REPLACE
---------
ali mohammed khan

 

TRANSLATE

This function is used to encrypt characters. For example you can use this function to replace characters in a given string with your coded characters.

Example

The following query replaces characters A with B, B with C, C with D, D with E,...Z with A, and a with b,b with c,c with d, d with e ....z with a.

select translate('interface','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
      'BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') “Encrypt” from dual;

Encrypt
-----------
joufsgbdf

SOUNDEX

This function is used to check pronounciation rather than exact characters. For example many people write names as “smith” or “smyth” or “smythe” but they are pronounced as smith only.

Example

The following example compare those names which are spelled differently but are pronouced as “smith”.

Select ename from emp where soundex(ename)=soundex('smith');

ENAME
---------
Smith
Smyth
Smythe

RPAD

Right pads a given string with a given character to n number of characters.

Example

The following query rights pad ename with '*'  until it becomes 10 characters.

select rpad(ename,'*',10) from emp;

Ename
----------
Smith*****
John******
Mohammed**
Sami******

LPAD

Left pads a given string with a given character upto n number of characters.

Example

The following query left pads ename with '*'  until it becomes 10 characters.

select lpad(ename,'*',10) from emp;

Ename
----------
*****Smith
******John
**Mohammed
******Sami

LTRIM

Trims blank spaces from a given string from left.

Example

The following query returns string “       Interface        “ left trimmed.

select ltrim('       Interface       ') from dual;

Ltrim
--------------
Interface 

RTRIM

Trims blank spaces from a given string from Right.

Example

The following query returns string “       Interface        “ right trimmed.

select rtrim('       Interface       ') from dual;

Rtrim
------------
   Interface 

TRIM

Trims a given character from left or right or both from a given string.

Example

The following query removes zero from left and right of a given string.

Select trim(0 from '00003443500') from dual;

Trim
----------
34435

CONCAT

Combines a given string with another string.

Example

The following Query combines ename with literal string “ is a “ and jobid.

Select concat(concat(ename,' is a '),job) from emp;

Concat
----------------
Smith is a clerk
John is a Manager
Sami is a G.Manager

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved