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