CHARACTER FUNCTIONS IN ORACLE

These functions mainly operate on character data.

1. UPPER : It is used to convert a string or column values to uppercase

Syntax - UPPER(string)

Example - 

SQL> select upper('hello') from dual;

HELLO

SQL> select upper(ename) from emp;

2. LOWER : It is used to convert to lowercase

Example - 

SQL> select lower("ABC') from dual;

abc

SQL> update emp set ename=lower(ename);

14 rows updated

3. INITCAP : It returns first character as capital and all remaining as small.

Example -

SQL> select initcap(ename) from emp;

Smith

4. LENGTH : It returns number data-type i.e. it returns total length of the string including spaces.

Example -

SQL> select length('AB CD') from dual;

5

Write a query to display the employees whose ename length is above 4 characters from emp by using length()

SQL> select * from emp where length(ename)>4;

5. SUBSTR : It will extract portion of the string from the given string based on last two parameters.

Example -

SQL> select substr('ABCDEFGHIJ',2,3) from dual;

BCD

SQL> select substr('ABCDEFGHIJ',-2,3) from dual;

GH

SQL> select substr('ABCDEFGHIJ',-5) from dual;

FGHIJ

Write a query to display the employees whose ename second letter will be capital 'AR' from emp table by using substring function

SQL> select ename from emp where substr(ename,2,2)='AR';

Note : In all databases we are not allowed to use group functions in where clause but we are allowed to use number function, character function, date function in where clause.

Example - select * from emp where sal=max(sal);

ERROR : group function is not allowed here.

6. INSTR : This function returns number data-type. That is it returns position of the delimiters, position of the characters, position of the string within given string.

Example -

SQL> select instr('AB*CD','*') from dual;

3

SQL> select instr('ABCDEFGHCDJKMCDNW','CD',-5,2) from dual;

3

SQL> select instr('ABCDEFGHCDJKMCDNW','CD',-4,2) from dual;

9

INSTR function always returns position based on last two parameters but oracle server finally counts number of characters from left side first position onwards.

7. LTRIM : It is used to remove characters on the left side of the given string.

Syntax - ltrim(colname or string,{set of characters})

SQL> select ltrim('SSMISSTHSS','S') from dual;

MISSTHSS

SQL> select job, ltrim(job,'CSM') from emp;

CLERK              CLERK

SALESMAN     ALESMAN

MANAGER      ANAGER

8. RTRIM :

SQL> select rtrim('SSMISSTHSS','S') from dual;

SSMISSTH

9. TRIM : Oracle 8i introduces trim() function. It is used to remove left and right side specified characters.

Synatx - trim('character' from 'string')

SQL> select trim('S' from 'SSMISSTHSS' )from dual;

MISSTH

Note : We can also convert trim() into ltrim by using leading clause and also convert trim() into rtrim by using trailing clause.

SQL> select trim(leading 'S' from 'SSMISSTHSS') from dual;

MISSTHSS

SQL> select trim(trailing 'S' from 'SSMISSTHSS') from dual;

SSMISSTH

Note : using trim function we can also remove leading, trailing spaces.

SQL> select length(trim('   abc   ')) from dual;

3