Functions

The tables in this section show the relationship between Oracle and SQL Server functions. Although the names may appear to be the same, it is important to note that functions may vary in numbers and types of arguments.

Number/Mathematical Functions

Function	Oracle	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
Degrees	N/A	DEGREES
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
PI	N/A	PI
Power	POWER	POWER
Radians	N/A	RADIANS

Function	Oracle	SQL Server
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

Function	Oracle	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
Returns starting point of character in character string (from right)	N/A	RIGHT
String compare and difference	N/A	DIFFERENCE
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

Function	Oracle	SQL Server
Repeat character string multiple times	RPAD	REPLICATE
Reverse character string	N/A	REVERSE
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	DATALENGTH
Greatest character string in list	GREATEST	N/A
Least character string in list	LEAST	N/A
Convert string if NULL	NVL	ISNULL

Date Functions

Function	Oracle	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

Function	Oracle	SQL Server
Date round	ROUND	CONVERT
Date truncate	TRUNC	CONVERT
Character string to date	TO_DATE	CONVERT
Convert date if NULL	NVL	ISNULL

Conversion Functions

Function	Oracle	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

Function	Oracle	SQL Server
Return first non-null expression	DECODE	COALESCE
Defined length of column	N/A	COL_LENGTH
Name of column from column ID	N/A	COL_NAME
Database ID from database name	N/A	DB_ID
Database name from database ID	N/A	DB_NAME
Database default nullability	N/A	GETANSINULL
Workstation ID number	N/A	HOST_ID
Workstation name	N/A	HOST_NAME
Identity column increment	N/A	IDENT_INCR
Identity column seed	N/A	IDENT_SEED
Current sequence value	CURRVAL	N/A
Next sequence value	NEXTVAL	N/A
Function	Oracle	SQL Server
Name of index column based on ID	N/A	INDEX_COL
If exp1 = exp2, return null	DECODE	NULLIF
Object ID number	N/A	OBJECT_ID
Object name based on object ID	N/A	OBJECT_NAME
Date index stats last updated	N/A	STATS_DATE
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	USER	USER
User environment (audit trail)	USERENV	N/A
Level in CONNECT BY clause	LEVEL	N/A
Text/image pointer (binary format)	N/A	TEXTPTR
Validate text/image pointer	N/A	TEXTVALID

Row Aggregate Functions

Function	Oracle	SQL Server
Average	N/A	AVG
Count	N/A	COUNT
Maximum	N/A	MAX
Minimum	N/A	MIN
Summation	N/A	SUM

Aggregate Functions

Function	Oracle	SQL Server
Average	AVG	AVG
Count	COUNT	COUNT
Maximum	MAX	MAX
Minimum	MIN	MIN
Standard deviation	STDDEV	N/A
Summation	SUM	SUM
Variance	VARIANCE	N/A

CASE and DECODE Functions

Use the CASE expression to replace the DECODE statement in your application code. The table shows the syntax for each statement.

DECODE	CASE
DECODE (test_value, expression1, value1 [[,expression2, value2] […]] [,default_value] )	CASE test_value WHEN expression1 THEN value1 [[WHEN expression2 THEN value2] [...]] [ELSE default_value] END

Both the DECODE statement and the 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 an example of a converted DECODE statement.

Oracle

SQL Server

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

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 statement does not allow. For more information about the CASE expression, see the Microsoft SQL Server Transact-SQL Reference.

CONVERT Function

The SQL Server CONVERT function is a multiple purpose conversion function. It converts an expression of one data type to another data type, and it supports a variety of special date formats.

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

It performs 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 255. If a length is not specified for these data types, the value of 30 is used.

Conversion	Oracle	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 CONVERT function, see the Microsoft SQL Server Transact-SQL Reference.

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)

Without century	With century	Standard	Output
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

SQL Server does not support the use of user-defined PL/SQL functions that can be called from SQL statements. This functionality can often be achieved in other ways.

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

Oracle	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.