Character Functions in SQL

CHARACTER  FUNCTIONS : -Oracle provides a variety of functions for working with strings in SQL.These functions accepts literal strings of characters(‘abcd’,’xyz’ etc.)or name of the character columns and return both character and numeric values.The various character functions are described below

1 -  Command :- ASCII

       Purpose :- It returns the ASCII value of first character in the string.

      Example :- 

 

 

  2-Command :- CHR

     Purpose : - It returns the character with ACII value of integer.   

    Example :-

   

  3- Command :- CONCAT(m,n)

       Purpose :-  It concatenates ‘m’ with ‘n’ where ‘m’ or ‘n’ can be either Column   

                              names or literals.

      Example :-

4 – Command :- INITCAP

      Purpose :-It changes the first letter of a word or series of words into upper case,and remaining letters into lower case.

     Example :-

5- Command :- INSTR

     Purpose :- It finds the location of a set of characters in a string.

     Example :-

6- Command :- LENGTH

    Purpose :- It returns the number of characters in the strings.

   Example :-

7- Command :- LOWER

     Purpose. :- It converts every letter in a string to lower case.   

    Example :-

8- Command :- UPPER

     Purpose :- It converts every letter in a string to upper case.

    Example :- 

9- Command :- LPAD

    Purpose :- It makes a string of a certain length by adding certain set of 

                        characters to the left of the string.

    Example :-

10- Command :- RPAD

       Purpose :-  :- It makes a string of a certain length by adding certain set of 

                        characters to the right of the string.

      Example :-

 

11 – Command :- LTRIM

         Purpose : -  It trims leading spaces or a designated character on the left

                              Side of the string.

     Example :-

12- Command :- RTRIM

       Purpose :-  It trims leading spaces or a designated character on the right

                            Side of the string.

       Example :-

13- Command :- REPLACE

       Purpose :- It replaces a character in a string with zero or more characters.

      Example :-

14- Command :- SUBSTR

       Purpose :- It is used to extract a piece of character string.

      Example :-