SQL Functions

SQL FUNCTIONS

LOWER function:

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

    Output:SQL_FUNCT1
  • 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.

    Output:SQLFUN2

    UPPER Function:

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

      Output:SQLFUN3

      INITCAP Function:

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

        Output:SQLFUN4

        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 ‘||’.

        Output:SQLFUN5

        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.

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

            Output:SQLFUN7

            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.

              Output:SQLFUN8

              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:

              Output: SQLFUN9

               

              Output:SQLFUN10

              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 “.”.

              Output:SQLFUN11

              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 ”.”

               

               

              Output:SQLFUN12

              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.

              It actually removes the individual occurrences of ‘x’ and ‘y’, as opposed to the pattern of  ‘xy’.

              Output:SQLFUN13

              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.

              It actually removes the individual occurrences of ‘x’and ‘y’, as opposed to the pattern of ‘xy’.

            • Output:SQLFUN14TRIM: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.

              Output:SQLFUN15

              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:

              Output:SQLFUN16

              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.

              Output:SQLFUN17

              ASCII Functions:

              The ascii function returns the NUMBER code that represents the specified character.

              Output:SQLFUN18

              SIGN Function:

              The sign function returns a value indicating the sign of a number.

              Output:SQLFUN19

              ADD MONTHS Function:

              The add_months function returns a date plus n months.

              Output:SQLFUN20

              MONTHS BETWEEN Function:

              The months_between function returns the number of months between date1 and date2.

              Output:SQLFUN21

              NEXT DAY Function:

              The next_day function returns the first weekday that is greater than a date.

              Output:SQLFUN22

              LAST DAY Function:

              The last_day function returns the last day of the month based on a date value.

              Output:SQLFUN23

              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.

              Output:SQLFUN24

              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.

              Output:SQLFUN25

              SYSDATE using different Parameters:

               

              The sysdate function returns the current system date and time on your local database.

              Output:SQLFUN26

              TO NUMBER Function:

              The to_number function converts a string to a number.

              Output:SQLFUN27

               

               

     

Related posts:

Leave a Reply