CHARACTER FUNCTIONS

|| or CONCAT :

Concatenates two strings together. The ‘|’ symbol is called as vertical bar or pipe

Syntax : string1 || string2 ( for || Function)

Syntax : Concat(string1 , string2) ( for concat Function)

Example : select concat ( city, country) from location;

is same as  select city || country from location;

ASCII :

This Function Returns The Ascii Code Value Of The Left Most Character From The Given Character Expression

Syntax : Ascii(Character Expression)

Example : select Ascii(‘a’) from dual ;

RESULT:  97

select Ascii(‘A’) from dual ;

 RESULT:  65

CHR:

This Function Returns The Ascii Character For The Given Ascii Value

Syntax : Chr(Ascii Value)

Example : select chr(65) from dual ;

RESULT:  A

Select chr(97) from dual ;

RESULT:  a

LENGTH:

This Function Is Used To Find The Length Of The Given Character Expression

Syntax : Length ( character expression )

Example : select length (‘sairam’) from dual;

RESULT:  6

select length (ename) from emp;

RESULT:  5

UPPER:

This Function Is Used To Convert All Characters In To Upper Case

Syntax : Upper (Character Expression)

Example : select upper (‘sairam’) from dual;

RESULT:  SAIRAM

LOWER:

This Function Is Used To Convert All Characters In To Lower Case

Syntax : Lower (Character Expression)

Example : select lower (‘SAIRAM’) from dual;

RESULT:  sairam

LTRIM:

This Function Removes Any Spaces From The Left Side of The String

Syntax : Ltrim(String)

Example : select Ltrim(' sairam') from dual;

RESULT: sairam

RTRIM:

This Function Removes Any Space From The Right Side of The String

Syntax : Rtrim(String)

Example : select Rtrim('sairam ') from dual;

RESULT: sairam

 TRIM:

If You Are Trimming The Exact Same Data From Both The Beginning And Then End Of The String, Then You Can Use The Trim Function In Place Of An Ltrim/Rtrim Combination

Syntax : Trim(String)

Example: select Trim(' sairam ') from dual;

RESULT: sairam

SUBSTR:

This function returns a part of the string from the specified Position to the specified number of characters

Syntax: Substr(String, Start Postion,Number of Characters)

Example : select substr('disk operating system',6,9) from dual;

RESULT: operating

LPAD:

This function is used to append the given text to the left side of any

column or String or lpad function allows you to “pad” the left side of a column

with any set of Characters.

Syntax : Lpad (<Expression>,<Size>,<String Expression>)

Example : select lpad(sal,7,'Rs. ') from emp;

RESULT: Rs. 800

Example : select lpad(sal,10,'Rs. ') from emp;

RESULT: Rs. Rs. 800

Rpad:

This function is used to append the given text to the right side of any column or string or lpad function allows you to “pad” the left side of a column with any set of characters.

Syntax : Rpad(<Expression>,<Size>,<String Expression>)

Example : select Rpad(sal,7,'Rs. ') from emp;

RESULT: 800Rs.

Example : select Rpad(sal,10,'Rs. ') from emp;

RESULT: 800Rs. Rs.

INITCAP:

This function takes the initial letter of every word in a string or column and converts just those letters to upper case.

Syntax : initcap (String)

Example : select Initcap(ename) from emp;

select Initcap(“WELCOME TO ALL”) from emp;

RESULT:  Welcome To All

TRANSLATE :

This function is used to translate the source expression into target expression that is present in the main string

Syntax : Translate(Main String, Source Expression, Target Expression)

Example : select Translate ('jack','j','b') from dual;

 RESULT:  back

select Translate ('back and bill','b','j') from dual;

RESULT: jack and jill

it will translate only one char