SQL Server Functions and Oracle Equivalent

Introduction

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

String Functions

SQL Server has a variety of string functions that are very useful for manipulating the string data type. SQL Server uses various data types to store the string and character types of data (in other words, varchar, nvarchar, and char). So we can use the string functions to get the desired and specific results. Find more about Functions in SQL Server here: Functions in SQL Server.

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:

LOWER (expr)

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

Oracle's Equivalent

UPPER(expr)

The UPPER Function converts the lowercase expression to uppercase.

Oracle's Equivalent

The equivalent upper function in Oracle is UPPER.

LTRIM(string)

The LTRIM function in SQL Server removes leading spaces.

Oracles Equivalent

RTRIM(string)

The RTRIM function removes trailing spaces in SQL Server.

Oracle's Equivalent

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

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.

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 can use the SUBSTR function again.

We use the function above to get the last five characters at the end of the string.

LPAD Function

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

Oracle's Equivalent

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

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 characters on the right side.

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 one, 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.

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.

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

ISNUMERIC(expression)

The ISNUMERIC function in SQL Server returns a value of 1, indicating that the given expression is a numeric value. It returns a value of 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 values.

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;
/

ABS(number)

Returns the absolute value of a number.

Oracle's Equivalent

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

CEILING (number)

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

Oracle's Equivalent

The CEILING function's equivalent in Oracle is CEIL.

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

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 the SYSDATE function.

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)

MONTH(date)

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

Oracle's Equivalent

(Or)

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 - The Style to convert the date to USA Standard format is in the query.

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 the 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

This article taught us some SQL Server functions and Oracle's Equivalent SQL. I hope this article might help to learn a little.