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 ofa 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 isa 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 Styleto 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.
