Single-Row Functions (Character Functions)

Character functions operate on values of dataype CHAR or VARCHAR

lets Talk about different character functions

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
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
Miscellaneous Single Row Functions
COALESCE
Coalesce function returns the first not null value in the expression list.
Example.
The following query returns salary+commision, if commission is null then returns salary, if salary is also null then returns 1000.
select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from emp;
ENAME SALARY COMM NET SAL
----- ------ ---- -------
SMITH 1000 100 1100
SAMI 3000 3000
SCOTT 1000
RAVI 200 1000
DECODE
DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)
Decode functions compares an expr with search value one by one. If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.
Example
The following query returns the department names according the deptno. If the deptno does not match any of the search value then returns “Unknown Department”
select decode(deptno,10,'Sales',20,'Accounts,30,'Production,
40,'R&D','Unknown Dept') As DeptName from emp;
DEPTNAME
----------
Sales
Accounts
Unknown Dept.
Accounts
Production
Sales
R&D
Unknown Dept.
GREATEST
GREATEST(expr1, expr2, expr3,expr4...)
Returns the greatest expr from a expr list.
Example
select greatest(10,20,50,20,30) from dual;
GREATEST
--------
50
select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;
GREATEST
--------
TANYA
LEAST
LEAST(expr1, expr2, expr3,expr4...)
It is simillar to greatest. It returns the least expr from the expression list.
select least(10,20,50,20,30) from dual;
LEAST
--------
10
select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;
LEAST
--------
RAVI
NVL
NVL2(expr1,expr2)
This function is oftenly used to check null values. It returns expr2 if the expr1 is null, otherwise returns expr1.
Example
The following query returns commission if commission is null then returns 'Not Applicable'.
Select ename,nvl(comm,'Not Applicable') “Comm” from dual;
ENAME COMM
------ ----
Scott 300
Tiger 450
Sami Not Applicable
Ravi 300
Tanya Not Applicable
NVL2
NVL2(expr1,expr2,expr3)
NVL2 returns expr2 if expr1 is not null, otherwise return expr3.
Example
The following query returns salary+comm if comm is not null, otherwise just returns salary.
select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;
SALARY COMM INCOME
------ ---- ------
1000 100 1100
2000 2000
2300 200 2500
3400 3400
NULLIF
NULLIF(expr1, expr2)
Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.
Example.
The following query shows old jobs of those employees who have changed their jobs in the company by comparing the current job with old job in oldemp table.
Select ename,nullif(e.job,o.job) “Old Job” from emp e, oldemp o where e.empno=o.empno;
ENAME OLD JOB
----- -------
SMITH CLERK
SAMI
SCOTT MANAGER
UID
Returns the current session ID of user logged on.
Example
select uid from dual;
UID
----
20
USER
Returns the username of the current user logged on.
select user from dual;
USER
---------
SCOTT
SYS_CONTEXT
SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.
EXAMPLE
The following query returns the username of the current user.
Select sys_context('USERENV','SESSION_USER') “Username” from dual;
USERNAME
---------
SCOTT
Similar to SESSION_USER parameter for namespace USERENV the other important parameters are
ISDBA :To check whether the current user is having DBA privileges or not.
HOST :Returns the name of host machine from which the client is connected.
INSTANCE :The instance identification number of the current instance
IP_ADDRESS: IP address of the machine from which the client is connected.
DB_NAME :Name of the database as specified in the DB_NAME initialization parameter
VSIZE
VSIZE(expr)
Returns the internal representation of expr in bytes.
Example
The following query return the representation of ename in bytes.
select ename,vsize(ename) as Bytes from emp;
ENAME BYTES
------ ------
SCOTT 5
SAMI 4
RAVI 4
KIRAN 5