Lesson : 19 - Character type Functions

1) ASCII

Syntax :

ASCII( string)

Returns ASCII values of first character of given string. ASCII stands for Americal Standard Code For Information Interchange.

Examples :

SQL>select ASCII('A') from dual;

65

SQL>select ASCII('America') from dual;

65

SQL>select ASCII(5) from dual;

53

2) CHR

Syntax :

CHR(ASCII value)

Retuns character equivalent to given ASCII values.

Exmaples :

SQL>select CHR(65) from dual;

A

SQL>select CHR(75) from dual;

K

3) CONCAT

Syntax :

CONCAT(string1, string2)

CONCAT returns String1 concatenated with String2. Both String1 and String2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its data type depends on the data types of the arguments.

If one of the arguments is a national data type, then the returned value is a national data type. For example:

•CONCAT(CLOB, NCLOB) returns NCLOB

•CONCAT(NCLOB, NCHAR) returns NCLOB

•CONCAT(NCLOB, CHAR) returns NCLOB

•CONCAT(NCHAR, CLOB) returns NCLOB

Examples :

SQL>select concat('Good','Work') from dual;

CONCAT('

--------

GoodWork

SQL>select concat('Employee name is : ', First_name) "Example " from emp;

Example

---------------------------------------

Employee name is : Arun

Employee name is : Sri

4) INITCAP

Syntax :

INITCAP (char type data)

Returns first letter of each word in uppercase, all other letters in lowercase.

Examples :

SQL>select initcap('This is nothing but testing') from dual;

INITCAP('THISISNOTHINGBUTTE

---------------------------

This Is Nothing But Testing

5) LOWER

Syntax :

Lower(char type data)

Returns lower case of given string.

Examples :

SQL>select lower('TESTING LOWER') from dual;

LOWER('TESTIN

-------------

testing lower

SQL>select lower('Lary Wills') from dual;

LOWER('LAR

----------

lary wills

SQL>select lower(first_name) from emp;

LOWER(FIRST_NAME)

--------------------

arun

sri

kamesh

6) INSTR

Syntax :

INSTR(string1, substring, starting position, nth occurence)

Search the substring in string1 and returns the starting position of substrong in the string1.

3rd parameter is optional, if it is specified, it denotes search starting position in the string1

4th parameter is also optional, if it mentioned, it denotes nth occurance , where n is positive number.

Examples :

SQL> select instr('Welcome to world of oracle','o') from dual;

5

SQL> select instr('Welcome to world of oracle','o',1) from dual;

5

SQL> select instr('Welcome to world of oracle','o',7) from dual;

10

SQL> select instr('Welcome to world of oracle','o',1,2) from dual;

10

SQL> select instr('Welcome to world of oracle','o',1,3) from dual;

13

7) LENGTH

Syntax :

LENGTH (char type data)

Returns length(number of characters) in given string.

Examples :

SQL> select length('oracle') from dual;

6

SQL> select length('oracle test') from dual;

11

Note : It counts space also.

8) LPAD

Syntax :

LPAD( string1, length, string to use pad )

It pads left side of the string1 with third parameter. 2nd parameter is the total length.

3rd parameter is optional and default is space.

Examples :

SQL> select lpad('Oracle',3) from dual;

Ora

SQL> select lpad('Oracle',10) from dual;

Oracle

SQL> select lpad('Oracle',10,'*') from dual;

****Oracle

SQL> select lpad('Oracle',10,'*$') from dual;

*$*$Oracle

SQL> select lpad(12345,10,'#') from dual;

#####12345

9) LTRIM

Syntax :

LTRIM( string1, string2 )

- Removes space in the left side of string1 if 2nd parameter is not specified.

string1 is the string to trim the characters from the left-hand side.

string2 is optional paramers, is the string that will be removed from the left-hand side of string1. If this parameter is omitted, the ltrim function will remove all leading spaces from string1.

Example :

SQL> select ltrim(' SQl*Plus') from dual;

SQl*Plus

SQL> select ltrim(' SQl*Plus','*') from dual;

SQl*Plus

SQL> select ltrim('*****SQl*Plus','*') from dual;

SQl*Plus

SQL> select ltrim('$234.50','$') from dual;

234.50

10) REPLACE

Replaces a sequence of characters in a string with another set of characters

Syntax :

REPLACE( string1, string_to_replace, string used for replace )

3rd parameter is optional.

Example :

SQL> select replace('abcdabcd','b') from dual;

acdacd

SQL> select replace('abcdabcd','b','x') from dual;

axcdaxcd

11) RPAD

Syntax :

LPAD( string1, length, string to use pad )

It pads right side of the string1 with third parameter. 2nd parameter is the total length.

3rd parameter is optional and default is space.

SQL> select rpad('Oracle',10) from dual;

Oracle

SQL> select rpad('Oracle',10,'*') from dual;

Oracle****