SQL Functions Part2 (Character Functions)

We have many character functions available in SQL which are very useful while working with the SQL statements. In this post I will discuss about those functions.

As the name it self specifying, character functions will work on character data . Character functions will accept character data as input and will produce output as either character or numeric data based on the type of function we are using.

Basically character functions are divided into two types.

    1. Case manipulation Functions
    2. Character manipulation Functions

Case manipulation Functions ::

   Case manipulation functions will work on the case of the characters. These are used for case conversions.

We have three functions available under this category.

   a) UPPER()
   b) LOWER()
   c) INITCAP()

UPPER() :

   This function will convert all the characters of input string to upper case letters. For this function input is a character and output is also a character.

 Syntax :  UPPER(i/p string)

 Ex : SELECT UPPER('Ram Babu') FROM dual;

 This will give result as 'RAM BABU'.

 LOWER() :

    This function will convert all the characters of input string to lower case letters. For this function input is a character and output is also a character.

 Syntax :  LOWER(i/p string)

 Ex : SELECT LOWER('Ram Babu') FROM dual;

 This will give result as 'ram babu'.

 INITCAP() :

     This function will convert first character of input string to upper case letter and remaining characters to lower case letters. For this function input is a character and output is also a character.

 Syntax :  INITCAP(i/p string)

 Ex : SELECT INITCAP('rAM Babu') FROM dual;

 This will give result as 'Ram babu'.   
      
  These are the three case manipulation functions available in SQL. These are very useful in writing SELECT statements. For example let us say we want to find out all the employees from employee table whose name is 'Abc'. As I already discussed in my previous posts character comparison is case sensitive. We don't know how the names were stored in the employee table. the same name Abc can be stored in many ways like ABC, abc, Abc. But we want all those results. In this case case manipulation functions will help us.

Query for this scenario can be written as,

  SELECT * FROM Emp WHERE upper(ename) = 'ABC';

 In this query I am converting the ename to upeer case letters and comparing it with 'ABC'.So, in this query irrespective of case of names in the table we will get all the employees with name abc using the above query.

So, like this we can work with case manipulation functions.

Character manipulation Functions ::

   Character manipulation functions will accept character inputs and will produce either character or numeric outputs. We have many character manipulation functions available in SQL. I will discuss important character manipulation functions in this post.

CONCAT() Function :

   This function is used to combine two strings into a single string. For this function input is a character string and output is also a character string. Using this function we can combine only two strings at a time.

  Syntax :  CONCAT ( str1, str2 )

 Ex : SELECT CONCAT ('Ram','Babu') FROM dual;

 This will give the output as 'RamBabu'.

 If I want to combine more than two strings then I have to go for nesting of functions.

 Nesting of functions ::

   If we call a function inside another function, then it will be called as nesting of functions. In this we will give output of one function as input to other function.

So, in case of CONCAT() function if I want to combine more than two strings then I have to go for nesting.

  CONCAT(str1,CONCAT(str2,str3))

Ex : SELECT CONCAT ('Kandimalla',CONCAT('Ram','Babu')) FROM dual;

  So, this will give output as KandimallaRamBabu. Here I gave output of the one CONCAT function as input to another CONCAT function. Like this I can do nesting of the functions.

SUBSTR() Function :

  This function is used to extract some part of input string. This function will accept character input and will produce character output. Syntax for this is,

 Syntax : SUBSTR(i/pstring , starting_pt_of_extraction, o/pstring_length)

 Here in this syntax,

  --> starting_pt_of_extraction is a number which will specify the position in input string from which the
        extraction should be started.
  --> o/pstring length will decide number of characters to be there in output string.

 Ex : SELECT SUBSTR('Hello World', 3, 5) From dual;

  Here we gave second argument as 3. It means extraction should start from 3rd position. So, it wil start from letter 'l'. we gave 3rd argument as 5. It means we need 5 characters to be present in output. So, we
will get output as,

    'llo W'.

This is how substring function will work.

 * If we exclude 3rd argument in the syntax, then by default total string from the starting point of extraction
    will be extracted.

   Ex : SELECT SUBSTR('Hello World',3) FROM dual;

  This will give output as,  'llo World'.

 We can extract the strings from ending also using SUBSTR function. For this we just need to give two arguments. One is input string and other one is number of characters to be there in output string (-ve value).

 Ex : SELECT SUBSTR('Hello World',-4) FROM dual;

 This will give output as, 'orld'.

 INSTR() Function :

     Instring function is used to find the position of a string in the input string. For this function input is a character string but the output is a number. (As we are finding position).

 Syntax :  INSTR (i/pstring, search_string, starting_pt_of_search, occurance)

  In this syntax,

  --> i/pstring refers to the Input string
  --> search_string refers to the string which we want to search in the input string
  --> starting_pt_of_search is a number which refers to the position from which we want to start searching
  --> occurance refers to which occurance of the string we want.

  This function will always give us the position of the string by calculating it from first position only irrespective of the starting_pt_of_search. This starting_pt_of_search is used for deciding occurance of the string. Let me explain this with an example.

 Let us say we have string 'Hello World'. In this string I want to search for string 'l'. But we have three 'l' s in the input string. So, which 'l' position we want ???. This will be decided by the occurance. So, if I say occurance as 1 it means I want position of first 'l'. If it is 2 then I want position of 2nd 'l'.
Again this occurance will be decided by starting_pt_of_search. If I start searching from 4th position then I will be having two occurances of 'l' only as one 'l' is in 3rd position. In any case final position of the string will be calculated from first position only.

 Ex : SELECT INSTR('Hello World','l',4,3) From dual;

  This will give result as 0. Because from 4th position we don't have 3rd occurance for 'l'.

    SELECT INSTR('Hello World','l',3,3) From dual;

  This will give result as 10. As third 'l' from position 3 is at 10th position in whole string.

    SELECT INSTR('Hello World','l',4,1) From dual;

  This will give result as 4. As 1st 'l' from position 4 is at 4th position in whole string.

If we exclude 4th argument in the syntax then by default it will give the position of first occurance of the string
specified by starting_pt_of_search.

   SELECT INSTR('Hello World','l',2) From dual;

  This will give us result as 3. As 1st 'l' from position 2 is at 3rd position in whole string.

If we exclude both 2nd and 3rd arguments in the syntax then by default we will get 1st occurance of the string from 1st position.

  SELECT INSTR('Hello World','l') From dual;

  This will give us result as 3. As 1st 'l' from position 1 is at 3rd position in whole string.

 This is how we can find the position of a string in the input string using INSTR() Function.

Length() Function :

    Length function is used to find the length of the input string in number of characters. For this function input is a character string and output is a number.

Syntax : Length(i/pstring)

 Ex : SELECT LENGTH('Hello World') FROM dual;

 This will give us the output as 11 as space is also a character.

LPAD() and RPAD() Functions :

   These functions as used to pad a character or set of characters to either left (LPAD) or right (RPAD) of the input string. This is similar to concatenation. But here we will concatenate same string number of times on either left or right side depending on the output length specified.

Syntax : LPAD(i/pstring, length_of_o/p_string, padding_string)
               RPAD(i/pstring, length_of_o/p_string, padding_string)

 Here in this syntax,

 --> length_of_o/p_string is a number which will specify the total length of output string we want after
       padding.
 --> padding_string will specify the string to be padded.

 Ex : SELECT LPAD('Ramu', 9 , '*') From dual;

  This will give output as, '*****Ramu' as we have mentioned total output string length as 9, we will get 5 stars padded to the input string on left side.

    SELECT RPAD('Ramu', 9 , '*') From dual;

  This will give us the output as, 'Ramu*****'.

 If we want to pad the same string on both sides of the input string then we have to use nesting of the functions concept. So, here first we have to use LPAD or RPAD and then we have to give output of these as input to RPAD or LPAD respectively to get desired result. This can be written as shown below.

  SELECT RPAD(LPAD('Ram',9,'*'),14,'*') From dual;

  This will give us the output as, '*****Ramu*****'.

If we have not specified the sufficient output string length to accomadate all the character of padding string then some part of string will be padded depending on the length.

  SELECT LPAD('Hello',7,'abcd') From dual;

  For this we will get output as 'abHello'. Even though we have 4 characters in the padding string, only two characters were padded to input string because of the output length we have specified.

TRIM(), LTRIM() and RTRIM() Functions :

   Trim() Function is used to trim heading and trailing characters from the input string.

 Syntax : TRIM(Leading or Trailing or both, trim_character from i/pstring)

   In this syntax,

   --> if we gave leading then character in first position will be compared for removal. If we gave trailing
         then character in last position will be compared for removal. if we gave 'both' then both first and
         last characters will be compared for removal.

   Ex : SELECT TRIM(Leading 'H' from 'Hello WorldH') From dual;

    This will give us output as 'ello WorldH'.

    SELECT TRIM(Trailing 'H' from 'Hello WorldH') From dual;

    This will give us output as 'Hello World'.

    SELECT TRIM('H' from 'Hello WorldH') From dual;

    This will give us output as 'ello World'. Here no need of specifying 'both' keyword as it is default.

  This TRIM() function can be used to remove heading and trailing spaces from the input string.

  Syntax : TRIM(i/pstring)

   So, if we give like this then both heading and trailing spaces if any will be removed from input string.

  Ex : SELECT TRIM('   Hello World    ') From dual;

   This will give output as 'Hello World'.

 LTRIM() function is used to remove all the spaces which are at the starting of the input string. (left side).

 Syntax : LTRIM(i/pstring)

  Ex : SELECT LTRIM('   Hello World    ') From dual;

   This will give output as 'Hello World    '.

 RTRIM() function is used to remove all the spaces which are at the ending of the input string. (right side).

 Syntax : RTRIM(i/pstring)

  Ex : SELECT RTRIM('   Hello World    ') From dual;

   This will give output as '    Hello World'.

REPLACE() Function :

  Replace function is used to replace some part of the input string with other string.

 Syntax : REPLACE(i/p string, search_string, replacement_string)

  Here in this syntax,

    ---> search_sting refers to the string which we want to replace in the input string
    ---> replacement_string refres to the replacement string for the search_string

  Ex : SELECT REPLACE('Ram Babu','Ram','Mahesh') From dual;

  This will give us the output as 'Mahesh Babu'. This is how we can use REPLACE function.

So, this is about the some of the important character functions available in SQL.

Note :Here in this post I have covered all the important character functions. If any information is required on any other character functions, post in comments. I will provide the required explanation.