SQL FUNCTIONS

• The lower function converts all letters in the specified string to lowercase.
  
  
  

  PgSQL

  1	SQL> SELECT 'ORACLE' String,LOWER('ORACLE') Lower FROM DUAL;

  
  Output:
• If the all employee names( Ename)and job columns was retrieved from the emp table by the below SQL, the value returned would be lower case only, and others columns data will display the normally.
• 
  
  
  

  PgSQL

  1	SQL> SELECT LOWER(Ename) Ename,LOWER(Job) Job,Sal,HireDate FROM Emp;

  
  Output:
  UPPER Function:
  • The Oracle UPPER function is used to convert strings to all upper case (i.e. capitals).
    
    
    

    PgSQL

    1	SQL> SELECT 'oracle' String,UPPER('oracle') Upper FROM DUAL;

    
    Output:
    INITCAP Function:
    • INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase.
      
      
      

      PgSQL

      1	SQL> SELECT 'oracle corporation' String, INITCAP('oracle corporation') InitCap FROM DUAl;

      
      Output:
      CONCAT Function:
      Oracle CONCAT( ) function only allows two arguments — only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using ‘||’.
      
      

      PgSQL

      1 2	SQL> SELECT 'Oracle' String1,'Corporation' String2,               CONCAT('Oracle', 'Corporation') Concat  FROM DUAL;

      Output:
      SUBSTR Function:
      • The substr functions allows you to extract a substring from a string.
      Syntax:
                 Substr(string,start_position,[length])
      • string is the source string.
      • start_position is the position for extraction. The first position in the string is always 1.
      • length is optional. It is the number of characters to extract. If this parameter is   omitted, substr will return the entire string.
      NOTE:
      • If start_position is 0, then substr treats start_position as 1 (ie: the first position in        the string).
      • If start_position is a positive number, then substr starts from the beginning of the string.
      • If start_position is a negative number, then substr starts from the end of the string and counts backwards.
      • If length is a negative number, then substr will return a NULL value.              Examples:
        • The below Query returns 5 characters starting in position 0.
          
          
          

          PgSQL

          1	SQL> SELECT 'ABCDEFGHIJ' String,SUBSTR ('ABCDEFGHIJ', 0, 5) SUBSTR FROM DUAL;

          
          Output:
        • The below query returns 3 characters starting in position 4 in job column from emp table
          
          
          

          PgSQL

          1	SQL> SELECT Ename, Job, Deptno FROM Emp WHERE SUBSTR (Job, 4, 3) = 'AGE';

          
          Output:
          LENGTH  Function:
          • The length function returns the length of the specified string.
          • Now we want to select the length of the values in the “ORACLE” column below.
            
            
            

            PgSQL

            1	SELECT 'ORACLE' String, LENGTH('ORACLE') Length FROM DUAL;

            
            Output:
            INSTR Function:
            • The instr function returns the location of a substring in a string.
            Syntax:
            Instr(string1,string2[,start_position[,nth_appearance]])
            • string1 is the string to search.
            • string2 is the substring to search for in string1.
            • start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts backstart_position number of characters from the end of string1 and then searches towards the beginning of string1.
            • nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.
            Note: If string2 is not found in string1, then the instr Oracle function will return 0.
            Examples:
            
            

            PgSQL

            1	SELECT 'STRING' Original,INSTR ('STRING' , 'R') InString FROM DUAL;

            Output: 
            
            

            PgSQL

            1	SQL>SELECT  'STRING' Original,INSTR('STRING' , 'RIN', 2) InString FROM DUAL;

            
            
            Output:
            Padding Functions LPAD, RPAD:
            LPAD: 
                     The lpad function pads the left-side of a string with a specific set of characters    (when string1 is not null).
            
            
              The following example left-pads a string with the characters “*” and “.”.
            
            

            PgSQL

            1	SQL>SELECT 'Page 1' MyString, LPAD('Page 1', 15, '*.') LPadded FROM DUAL;

            Output:
            RPAD:
            The rpad function pads the right-side of a string with a specific set of characters     (when string1 is not null).
             The following example right-pads a string with the characters “*” and ”.”
            
            
            
            

            PgSQL

            1	SQL> SELECT 'Page 1' String1,   RPAD('Page 1' , 15, '*.') RPad FROM DUAL;

            
            
            Output:
            Trim Functions LTRIM, RTRIM, TRIM:
            
            
            
            LTRIM:
            The ltrim function removes all specified characters from the left-hand side of a string.The ltrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.
            
            

            PgSQL

            1 2	SQL>SELECT 'xyzXxyLAST WORD' OrgStr,LTRIM('xyzXxyLAST WORD', 'xy')  Ltrim       FROM DUAL;

            It actually removes the individual occurrences of ‘x’ and ‘y’, as opposed to the pattern of  ’xy’.
            Output:
            RTRIM:
            The rtrim function removes all specified characters from the right-hand side of a string.The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the following example.
            
            

            PgSQL

            1	SELECT 'BROWNINGyxXxy' String,RTRIM('BROWNINGyxXxy', 'xy') Rtrim FROM DUAL;

            It actually removes the individual occurrences of ‘x’and ‘y’, as opposed to the pattern of ‘xy’.
          • Output:TRIM:The trim function removes all specified characters either from the beginning or  the ending of a string. The following query removes the character ”S” from a string.
            
            

            PgSQL

            1	SQL>SELECT 'MITHSS' String,TRIM('S' FROM 'MITHSS') Trimmed FROM DUAL;

            Output:
            Replace Function:
            The replace function replaces a sequence of characters in a string with another set of characters.The following example replaces occurrences of J with BL:
            
            

            PgSQL

            1	SELECT 'JACK AND JUE' OrgStr, REPLACE('JACK AND JUE' , 'J', 'BL') Replace FROM DUAL;

            Output:
            Translate Function:
            
            
            The translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.
            
            

            PgSQL

            1 2	SQL> SELECT   Ename, Job,TRANSLATE(Job, 'P', ' ') Translate          FROM Emp  WHERE Job = 'PRESIDENT';

            Output:
            ASCII Functions:
            The ascii function returns the NUMBER code that represents the specified character.
            
            

            PgSQL

            1	SQL>SELECT 'Ascii Code'||CHR(67)||CHR(65)||CHR(84)||CHR(10) Sample FROM DUAL;

            Output:
            SIGN Function:
            The sign function returns a value indicating the sign of a number.
            
            

            PgSQL

            1	SQL> SELECT Ename, Sal, Comm FROM Emp WHERE SIGN(Sal - Comm) = -1;

            Output:
            ADD MONTHS Function:
            The add_months function returns a date plus n months.
            
            

            PgSQL

            1 2	SQL>SELECT SYSDATE ToDay, ADD_MONTHS(SYSDATE, 3) "3 Months",      ADD_MONTHS(SYSDATE, -3) "-3 Months" FROM DUAL;

            Output:
            MONTHS BETWEEN Function:
            The months_between function returns the number of months between date1 and date2.
            
            

            PgSQL

            1	SQL>SELECT Ename, Sal, SYSDATE Today, HireDate,MONTHS_BETWEEN(SYSDATE, HireDate) "Months"FROM Emp;

            Output:
            NEXT DAY Function:
            The next_day function returns the first weekday that is greater than a date.
            
            

            PgSQL

            1 2 3 4 5 6

            SQL>SELECT SYSDATE Today,              NEXT_DAY(SYSDATE, 'FRI') "Friday",              NEXT_DAY(SYSDATE, 6) "Friday" ,              NEXT_DAY(SYSDATE, 'FRIDAY') "Friday" ,              NEXT_DAY(SYSDATE, 'WED') "Wednesday"   FROM DUAL;

            Output:
            LAST DAY Function:
            The last_day function returns the last day of the month based on a date value.
            
            

            PgSQL

            1	SQL>SELECT SYSDATE Today, LAST_DAY(SYSDATE) LastDay FROM DUAL;

            Output:
            ROUND and TRUNC Functions:
            TRUNC and ROUND function looks similar but not exactly.
            ROUND function used to round the number to the nearest while TRUNC used to truncate/delete the number from some position. Some cases both returns same result.
            
            

            PgSQL

            1	SQL>SELECT SYSDATE Today,ROUND(SYSDATE) Round,TRUNC(SYSDATE) Trunc FROM DUAL;

            Output:
            TO CHAR Function:
            The TO_CHAR function converts a DATETIME, number, or NTEXT expression to a TEXT expression in a specified format. This function is typically used to format output data.
            
            

            PgSQL

            1 2	SQL>SELECT Ename, Sal, Comm,TO_CHAR(Sal - Comm, '9999MI') Diff FROM Emp        WHERE Comm IS NOT NULL

            Output:
            SYSDATE using different Parameters:
            
            
            The sysdate function returns the current system date and time on your local database.
            
            

            PgSQL

            1 2 3 4

            SQL>SELECT Ename, Sal,HireDate||TO_CHAR(HireDate, ' B.C.') HireDate,              TO_CHAR(SYSDATE, 'P.M.') Meridian,              TO_CHAR(SYSDATE, ' B.C.')              FROM Emp;

            Output:
            TO NUMBER Function:
            The to_number function converts a string to a number.
            
            

            PgSQL

            1 2 3 4 5

            SQL>SELECT 'ALL ORACLE APPS' OrgName,              TRANSLATE('ALL ORACLE APPS',              'ABCDEFGHIJKLMNOPQRSTUVWXYZ',              '1234567890!@#$%^&*()-=_+;,.') EncryptedName         FROM DUAL;

            Output: