Functions

The tables in this section show the relationship between Oracle and SQL Server scalar-valued and aggregate functions. Although the names appear to be the same, it is important to note that functions vary in numbers and types of arguments. Also, functions that are supplied only by Microsoft SQL Server are not mentioned in this list as this chapter is limited to easing migration from existing Oracle applications. Examples of functions not supported by Oracle are: degrees (DEGREES), PI (PI), and random number (RAND).

Number/Mathematical Functions

The following are number/mathematical functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle Microsoft SQL Server
Absolute value ABS ABS
Arc cosine ACOS ACOS
Arc sine ASIN ASIN
Arc tangent of n ATAN ATAN
Arc tangent of n and m ATAN2 ATN2
Smallest integer >= value CEIL CEILING
Cosine COS COS
Hyperbolic cosine COSH COT
Exponential value EXP EXP
Largest integer <= value FLOOR FLOOR
Natural logarithm LN LOG
Logarithm, any base LOG(N) N/A
Logarithm, base 10 LOG(10) LOG10
Modulus (remainder) MOD USE MODULO (%) OPERATOR
Power POWER POWER
Random number N/A RAND
Round ROUND ROUND
Sign of number SIGN SIGN
Sine SIN SIN
Hyperbolic sine SINH N/A
Square root SQRT SQRT
Tangent TAN TAN
Hyperbolic tangent TANH N/A
Truncate TRUNC N/A
Largest number in list GREATEST N/A
Smallest number in list LEAST N/A
Convert number if NULL NVL ISNULL

Character Functions

The following are character functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle Microsoft SQL Server
Convert character to ASCII ASCII ASCII
String concatenate CONCAT (expression + expression)
Convert ASCII to character CHR CHAR
Returns starting point of character in character string (from left) INSTR CHARINDEX
Convert characters to lowercase LOWER LOWER
Convert characters to uppercase UPPER UPPER
Pad left side of character string LPAD N/A
Remove leading blanks LTRIM LTRIM
Remove trailing blanks RTRIM RTRIM
Starting point of pattern in character string INSTR PATINDEX
Repeat character string
multiple times		 RPAD REPLICATE
Phonetic representation of
character string		 SOUNDEX SOUNDEX
String of repeated spaces RPAD SPACE
Character data converted from numeric data TO_CHAR STR
Substring SUBSTR SUBSTRING
Replace characters REPLACE STUFF
Capitalize first letter of each word in string INITCAP N/A
Translate character string TRANSLATE N/A
Length of character string LENGTH DATELENGTH or LEN
Greatest character string
in list		 GREATEST N/A
Least character string in list LEAST N/A
Convert string if NULL NVL ISNULL

Date Functions

The following are date functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle Microsoft SQL Server
Date addition (date column +/- value) or
ADD_MONTHS		 DATEADD
Difference between dates (date column +/- value) or
MONTHS_BETWEEN		 DATEDIFF
Current date and time SYSDATE GETDATE()
Last day of month LAST_DAY N/A
Time zone conversion NEW_TIME N/A
First weekday after date NEXT_DAY N/A
Character string representation of date TO_CHAR DATENAME
Integer representation of date TO_NUMBER
(TO_CHAR))		 DATEPART
Date round ROUND CONVERT
Date truncate TRUNC CONVERT
Character string to date TO_DATE CONVERT
Convert date if NULL NVL ISNULL

Conversion Functions

The following are conversion functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle Microsoft SQL Server
Number to character TO_CHAR CONVERT
Character to number TO_NUMBER CONVERT
Date to character TO_CHAR CONVERT
Character to date TO_DATE CONVERT
Hex to binary HEX_TO_RAW CONVERT
Binary to hex RAW_TO_HEX CONVERT

Other Row-level Functions

The following are other row-level functions supported by Oracle and their Microsoft SQL Server equivalents.

Function Oracle Microsoft SQL Server
Return first nonnull expression DECODE COALESCE
Current sequence value CURRVAL N/A
Next sequence value NEXTVAL N/A
If exp1 = exp2, return null DECODE NULLIF
User’s login ID number UID SUSER_ID
User’s login name USER SUSER_NAME
User’s database ID number UID USER_ID
User’s database name USER USER_NAME
Current user CURRENT_USER CURRENT_USER
User environment (audit trail) USERENV N/A
Level in CONNECT BY clause LEVEL N/A

Aggregate Functions

The following are aggregate functions supported by Oracle and their SQL Server equivalents.

Function Oracle Microsoft SQL Server
Average AVG AVG
Count COUNT COUNT
Maximum MAX MAX
Minimum MIN MIN
Standard deviation STDDEV STDEV or STDEVP
Summation SUM SUM
Variance VARIANCE VAR or VARP

Conditional Tests

Both the Oracle DECODE statement and the Microsoft SQL Server CASE expression perform conditional tests. When the value in test_value matches any following expression, the related value is returned. If no match is found, the default_value is returned. If no default_value is specified, both DECODE and CASE return NULL if there is no match. The table shows the syntax as well as an example of a converted DECODE command.

Oracle Microsoft SQL Server
DECODE (test_value,
expression1, value1
[[,expression2, value2] […]]
[,default_value]
)

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(DECODE(grade
                    ,'A', 4
                    ,'A+', 4.3
                    ,'A-', 3.7
                    ,'B', 3
                    ,'B+', 3.3
                    ,'B-', 2.7
                    ,'C', 2
                    ,'C+', 2.3
                    ,'C-', 1.7
                    ,'D', 1
                    ,'D+', 1.3
                    ,'D-', 0.7
                    ,0)),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

 CASE input_expression
WHEN when_expression THEN    result_expression
[[WHEN when_expression THEN    result_expression] [...]]
[ELSE else_result_expression]
END

CREATE VIEW STUDENT_ADMIN.STUDENT_GPA
(SSN, GPA)
AS SELECT SSN, ROUND(AVG(CASE grade
                WHEN 'A' THEN 4
                WHEN 'A+' THEN 4.3
                WHEN 'A-' THEN 3.7
                WHEN 'B' THEN 3
                WHEN 'B+' THEN 3.3
                WHEN 'B-' THEN 2.7
                WHEN 'C' THEN 2
                WHEN 'C+' THEN 2.3
                WHEN 'C-' THEN 1.7
                WHEN 'D' THEN 1
                WHEN 'D+' THEN 1.3
                WHEN 'D-' THEN 0.7
                ELSE 0
                END),2)
FROM STUDENT_ADMIN.GRADE
GROUP BY SSN

The CASE expression can support the use of SELECT statements for performing Boolean tests, something the DECODE command does not allow. For more information about the CASE expression, see SQL Server Books Online.

Converting Values to Different Data Types

The Microsoft SQL Server CONVERT and CAST functions are multiple purpose conversion functions. They provide similar functionality, converting an expression of one data type to another data type, and supporting a variety of special date formats:

CAST(expression AS data_type)

CONVERT (data type[(length)], expression [, style])

CAST is a SQL-92 standard function. These functions perform the same operations as the Oracle TO_CHAR, TO_NUMBER, TO_DATE, HEXTORAW, and RAWTOHEX functions.

The data type is any system data type into which the expression is to be converted. User-defined data types cannot be used. The length parameter is optional and is used with char, varchar, binary, and varbinary data types. The maximum allowable length is 8000.

Conversion Oracle Microsoft SQL Server
Character to number TO_NUMBER('10') CONVERT(numeric, '10')
Number to character TO_CHAR(10) CONVERT(char, 10)
Character to date TO_DATE('04-JUL-97')
TO_DATE('04-JUL-1997',
'dd-mon-yyyy')
TO_DATE('July 4, 1997',
'Month dd, yyyy')		 CONVERT(datetime, '04-JUL-97')
CONVERT(datetime, '04-JUL-1997')
CONVERT(datetime, 'July 4, 1997')
Date to character TO_CHAR(sysdate)
TO_CHAR(sysdate, 'dd mon yyyy')
TO_CHAR(sysdate, 'mm/dd/yyyy')		 CONVERT(char, GETDATE())
CONVERT(char, GETDATE(), 106)
CONVERT(char, GETDATE(), 101)
Hex to binary HEXTORAW('1F') CONVERT(binary, '1F')
Binary to hex RAWTOHEX
(binary_column)		 CONVERT(char, binary_column)

Notice how character strings are converted to dates. In Oracle, the default date format model is “DD-MON-YY.” If you use any other format, you must provide an appropriate date format model. The CONVERT function automatically converts standard date formats without the need for a format model.

When converting from a date to a character string, the default output for the CONVERT function is “dd mon yyyy hh:mm:ss:mmm(24h)”. A numeric style code is used to format the output to other types of date format models. For more information about the CONVERT function, see SQL Server Books Online.

The following table shows the default output for Microsoft SQL Server dates.

Without Century With Century Standard Output
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default milliseconds mon dd yyyy hh:mi:ss:mmm (AM or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)

User-defined Functions

Oracle PL/SQL functions can be used in Oracle SQL statements. This functionality can often be achieved in other ways with Microsoft SQL Server.

In the following example, the Oracle user-defined function GET_SUM_MAJOR is used to obtain a sum of tuition paid by major. It can be replaced in SQL Server by using a query as a table.

Oracle Microsoft SQL Server
SELECT SSN, FNAME, LNAME, )    TUITION_PAID,
  TUITION_PAID/GET_SUM_
   MAJOR(MAJOR)
  AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT		 SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID/SUM_MAJOR AS PERCENT_MAJOR
FROM STUDENT_ADMIN.STUDENT,
(SELECT MAJOR,      SUM(TUITION_PAID) SUM_MAJOR
FROM STUDENT_ADMIN.STUDENT
GROUP BY MAJOR) SUM_STUDENT
WHERE STUDENT.MAJOR =      SUM_STUDENT.MAJOR
CREATE OR REPLACE FUNCTION GET_SUM_MAJOR
(INMAJOR VARCHAR2) RETURN NUMBER
AS SUM_PAID NUMBER;
BEGIN
SELECT SUM(TUITION_PAID) INTO    SUM_PAID
FROM STUDENT_ADMIN.STUDENT
WHERE MAJOR = INMAJOR;
RETURN(SUM_PAID);
END GET_SUM_MAJOR;		 No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax.