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.
   
   
   
   Note: In SQL Server the LEN function does not consider trailing blanks.
   
   
   
   Oracle's Equivalent
   
   The Oracle's equivalent is LENGTH. Dual is a special table in Oracle.
   
   SELECT LENGTH ('Telnet') FROM DUAL;
   
   
   
   In Oracle the Trailing Spaces are taken into account for determining the length of the string like this:
   
   
    
2. LOWER (expr)
   
   The LOWER function is self-explanatory; it converts upper case data to lower case for the given expression.
   
   
   
   Oracle's Equivalent
   
   
    
3. UPPER(expr)
   
   The UPPER Function converts the lower case expression to upper case.
   
   
   
   Oracle's Equivalent
   
   The equivalent upper function in Oracle is UPPER.
   
   
    
4. LTRIM(string)
   
   The LTRIM function in SQL Server removes leading spaces.
   
   
   
   Oracles Equivalent
   
   
    
5. RTRIM(string)
   
   The RTRIM function removes trailing spaces in SQL Server.
   
   
   
   Oracle's Equivalent
   
   
   
   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.
   
   
    
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)
   
   
   
   Oracle's Equivalent
   
   In Oracle, the SUBSTRING function gets a part of a string.
   
   
    

7. RIGHT (string, length)
   
   The right function in SQL Server returns the specified number of characters from the end of the string.
   
   
   
   Oracle's Equivalent
   
   There is no direct function to do this in Oracle; we again can use the SUBSTR function to do this.
   
   
   
   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.
   
   
   
   Oracle's Equivalent
   
   In Oracle, we have the LPAD function to pad the string with the specified set of characters.
   
   
    
9. RPAD Function
   
   To RPAD characters to a string we use the combination of the LEFT and REPLICATE functions.
   
   
   
   Oracle's Equivalent
   
   In Oracle, we have the RPAD function to pad the string with the specified set of characters on the right side.
   
   
    
10. SUBSTRING(string,start_position,length)
    
    The Substring function in SQL Server extracts a part of the string from the given string.
    
    
    
    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.
    
    
     
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.
    
    
    
    Oracle Equivalent
    
    Oracle has its REPLACE function with the same syntax as SQL Server.
    
    
     
12. REVERSE(string)
    
    The reverse function returns the given input string in reverse order.
    
    
    
    Oracle's Equivalent
    
    In oracle, we use the REVERSE function.
    
    
    

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
   
   
   
   Non-Numeric Expression
   
   
   
   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.
   
   
   
   Oracle's Equivalent
   
   In Oracle we have the ABS function as in SQL Server.
   
   
    
3. CEILING (number)
   
   The ceiling function returns an integer value that is greater than or equal to the given number.
   
   
   
   Oracle's Equivalent
   
   The CEILING function's equivalent in Oracle is CEIL.
   
   
    
4. FLOOR(number)
   
   The Floor function returns an integer value that is less than or equal to the number specified.
   
   
   
   Oracle's Equivalent
   
   SQL Server's equivalent of the FLOOR function in Oracle is FLOOR.
   
   
   

Date-Time Functions

1. GETDATE()
   
   The GETDATE () function returns the current System Date and Time.
   
   
   
   Oracle's Equivalent
   
   To get the current date and time in Oracle we use SYSDATE function.
   
   
    
2. DAY(date)
   
   The DAY function returns the day for the given date as an integer value.
   
   
   
   Oracle's Equivalent
   
   In Oracle we can use the TO_CHAR function or the EXTRACT function to do this.
   
   
   
   (Or)
   
   
    
3. MONTH(date)
   
   The Month function returns the month for the specified date as an integer value.
   
   
   
   Oracle's Equivalent
   
   
   
   (Or)
   
   
    
4. YEAR(date)
   
   The YEAR function returns the year part for the given date.
   
   
   
   Oracle's Equivalent
   
   
   
   (Or)
   
   
   
   Date-Time Conversion Functions
   
   Date to String
   
   To convert a DATE to VARCHAR in SQL Server we use the CONVERT function in SQL Server.
   
   
   
   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.
   
   
   
   String to Date
   
   In SQL Server we use the CONVERT function to convert between date to string as well string to date.
   
   
   
   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.
   
   
   

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.