SQL Server Functions and Oracle Equivalent

This article looks at SQL Server string, numeric and date functions and their Oracle equivalents. For Oracle I have used TOAD as an application tool to query the database.
String Functions

  1. LEN(string)

    The Length function in SQL Server gets the length of the string specified in the expression.

    image1.png

    Note: In SQL Server the LEN function does not consider trailing blanks.

    image2.png

    Oracle's Equivalent

    The Oracle's equivalent is LENGTH. Dual is a special table in Oracle.

    SELECT LENGTH ('Telnet') FROM DUAL;

    image3.png

    In Oracle the Trailing Spaces are taken into account for determining the length of the string like this:

    image4.png
     
  2. LOWER (expr)

    The LOWER function is self-explanatory; it converts upper case data to lower case for the given expression.

    image5.png

    Oracle's Equivalent

    image6.png
     
  3. UPPER(expr)

    The UPPER Function converts the lower case expression to upper case.

    image7.png

    Oracle's Equivalent


    The equivalent upper function in Oracle is UPPER.

    image8.png
     
  4. LTRIM(string)

    The LTRIM function in SQL Server removes leading spaces.

    image9.png

    Oracles Equivalent

    image10.png
     
  5. RTRIM(string)

    The RTRIM function removes trailing spaces in SQL Server.

    image11.png

    Oracle's Equivalent

    image12.png

    In Oracle, to remove leading and trailing spaces we use the TRIM function. In SQL Server we use the combination of LTRIM and the TRIM function to remove the leading and trailing spaces.

    image13.png
     
  6. LEFT (string, length)

    The left function in SQL Server returns a specified number of characters from the beginning of the string.

    SELECT LEFT('Keyboard',3)

    image14.png

    Oracle's Equivalent

    In Oracle, the SUBSTRING function gets a part of a string.

    image15.png
     

  7. RIGHT (string, length)

    The right function in SQL Server returns the specified number of characters from the end of the string.

    image16.png

    Oracle's Equivalent


    There is no direct function to do this in Oracle; we again can use the SUBSTR function to do this.

    image17.png

    To get the last five characters at the end of the string we simply use the function above.
     
  8. LPAD Function

    In SQL Server to do the LPAD function we use the combination of RIGHT and REPLICATE functions.

    image18.png

    Oracle's Equivalent

    In Oracle, we have the LPAD function to pad the string with the specified set of characters.

    image19.png
     
  9. RPAD Function

    To RPAD characters to a string we use the combination of the LEFT and REPLICATE functions.

    image20.png

    Oracle's Equivalent

    In Oracle, we have the RPAD function to pad the string with the specified set of characters on the right side.

    image21.png
     
  10. SUBSTRING(string,start_position,length)

    The Substring function in SQL Server extracts a part of the string from the given string.

    image22.png

    This gets the first two characters of the String "Lenovo". Here the start position is 1 so it starts with the first letter and returns the first two characters since the length specified is two.

    Oracle's Equivalent

    In Oracle we have the SUBSTR function to return the specified part of the substring.

    image23.png
     
  11. REPLACE (input_string ,string_to_replace, replacement_string)

    The replace function replaces a sequence of characters in a string with another set of characters.

    image24.png

    Oracle Equivalent

    Oracle has its REPLACE function with the same syntax as SQL Server.

    image25.png
     
  12. REVERSE(string)

    The reverse function returns the given input string in reverse order.

    image26.png

    Oracle's Equivalent

    In oracle, we use the REVERSE function.

    image27.png

Numeric Functions

  1. ISNUMERIC(expression)

    The ISNUMERIC function in SQL Server return a value 1 indicating that the given expression is a numeric value. It returns a value 0 otherwise.

    Numeric Expression

    image28.png

    Non-Numeric Expression

    image29.png

    Oracle's Equivalent

    In Oracle we use a combination of LEN, TRIM and TRANSLATE functions to check a string for a numeric value.

    SELECT LENGTH (TRIM (TRANSLATE ('1256.54', ' +-.0123456789',' ')))
    FROM DUAL;


    (Or)

    We can create a Custom function in Oracle to check for numeric value.

    CREATE OR REPLACE FUNCTION ISNUMERIC (PARAM IN CHAR) RETURN NUMBER AS
        DUMMY VARCHAR2 (100);
    BEGIN
        DUMMY := TO_CHAR(TO_NUMBER(PARAM));
        RETURN (1);
    EXCEPTION
        WHEN VALUE_ERROR THEN
            RETURN (0);
    END;
    /

     

  2. ABS(number)

    Returns the absolute value of a number.

    image30.png

    Oracle's Equivalent

    In Oracle we have the ABS function as in SQL Server.

    image31.png
     
  3. CEILING (number)

    The ceiling function returns an integer value that is greater than or equal to the given number.

    image32.png

    Oracle's Equivalent

    The CEILING function's equivalent in Oracle is CEIL.

    image33.png
     
  4. FLOOR(number)

    The Floor function returns an integer value that is less than or equal to the number specified.

    image34.png

    Oracle's Equivalent

    SQL Server's equivalent of the FLOOR function in Oracle is FLOOR.

    image35.png

Date-Time Functions

  1. GETDATE()

    The GETDATE () function returns the current System Date and Time.

    image36.png

    Oracle's Equivalent

    To get the current date and time in Oracle we use SYSDATE function.

    image37.png
     
  2. DAY(date)

    The DAY function returns the day for the given date as an integer value.

    image38.png

    Oracle's Equivalent

    In Oracle we can use the TO_CHAR function or the EXTRACT function to do this.

    image39.png

    (Or)

    image40.png
     
  3. MONTH(date)

    The Month function returns the month for the specified date as an integer value.

    image41.png

    Oracle's Equivalent

    image42.png

    (Or)

    image43.png
     
  4. YEAR(date)

    The YEAR function returns the year part for the given date.

    image44.png

    Oracle's Equivalent

    image45.png

    (Or)

    image46.png

    Date-Time Conversion Functions

    Date to String


    To convert a DATE to VARCHAR in SQL Server we use the CONVERT function in SQL Server.

    image47.png

    Here the output string is in the format mm/dd/yyyy which is the USA Standard format for specifying the date.

    101 - In the query is the Style to convert the date to USA Standard format.

    In Oracle, we use the TO_CHAR function to get the specified format.

    image48.png

    String to Date

    In SQL Server we use the CONVERT function to convert between date to string as well string to date.

    image49.png

    Without the Style specified the query will not return the correct date.

    Oracle's Equivalent

    To convert a string to date in Oracle we use the TO_DATE function.

    image50.png

Conclusion

In this article we have learned some of the SQL Server functions and their Oracle's Equivalent SQL. Hope this article might help to learn a little.