Upper():

  • It returns specified string into the upper case.

Syntax:

Upper(String or Filed);

Example:

SELECT upper('Welcome') FROM DUAL;    // with constant String 
SELECT upper(firstName) FROM EMP ;    // where firstName is a field

Output:

WELCOME             // Output for static string example

Lower():

  • It returns specified strings in to the lower case letters.

Syntax:

lower(String or Filed);

Example:

SELECT lower('WELCOME') FROM DUAL;    // with constant String 
SELECT lower(firstName) FROM EMP ;    // where firstName is a field

Output:

welcome             // Output for static string example

Length():

  • It returns total length or number of characters of the given string.

Syntax:

length(String or Filed);

Example:

SELECT length('WELCOME') FROM DUAL;    // with constant String 
SELECT length(firstName) FROM EMP ;    // where firstName is a field

Output:

7             // Output for static string example

SubStr():

  • It is use to find sub string from a given string.
  • It takes three arguments as follow.

Syntax:

substr(String, startPosition, noOfChar);
  • Where string is the string from which substring is to be found
  • Start position is the starting position from which Substring is to be found.
  • No of character specified total number of character to be extract from the stating position.

Example:

SELECT substr('Welcome', 4, 4) FROM DUAL;

Output:

come      // sub-string from 'Welcome' string

Instr():

  • It is use search the specific character in to the given string.

Syntax:

instr(String, charString, noOfChar);
  • Where string is the string in which searching will Perform.
  • noOfChar specifies characters to be search into the given string.
  • It returns index value on success.

Example:

SELECT instr (‘Welcome’, ’e’, 1) FROM DUAL;

Output:

2

 Rtrim():

  • It removes specified set of character from the given string on the right side.

 Syntax:

rtrim(string, <charSet>);

Example:

SELECT rtrim('Welcome', 'met')FROM DUAL;

Output:

Welco

Ltrim():

  • It removes specified set of character from the given string on the left side.

Syntax:

ltrim(string, <charSet>);

Example:

SELECT ltrim('Welcome', 'wet') FROM DUAL;

Output:

lcome

Translate():

  • It translates are replace specified character set with the string to be replacing from a given string.
  • It has one to one relationship for the replacement.

Syntax:

translate (<string1>, <string_to_replace>,<Replacement string>) ;

Example:

SELECT translate ( '1sct523', '123', '7a9' ) FROM DUAL ;

Output:

7sct5a9

Lpad ():

  • It is use to apply padding on the left side to given value.

Syntax:

lpad(string, n , [paddingChar])
  • String specifies target string to which padding is apply.
  • Second argument specifies no of padding.
  • Padding character specifies the character by which Padding will be applied.

 Example:

SELECT lpad(‘page 1’, 10, ’*’) FROM DUAL ;

Output:

****page 1

Rpad ():

  • Rpad applies padding from the right side to a given string.

 Syntax:

rpad(string, n , [paddingChar])
  • String specifies the target string to which padding is to be applied.
  • Second arguments specify the no of padding.
  • Padding characters specify the character by which padding will be apply.

Example:

SELECT rpad(‘page 1’, 10, ’*’) FROM DUAL ;

Output:

Page 1****

Initcap ():

  • It is used to convert given string into initial capital letters.
  • It will convert first character of each word into capital letter.

Syntax:

initcap (string);

Example:

SELECT initcap(‘welcome to oracle’) FROM DUAL ;

Output:

Welcome To Oracle