SQL Server vs Oracle: 45 Essential Function Equivalents

Numeric Functions

Absolute Value – SELECT ABS(-1) AS value (SQL Server) vs SELECT ABS(-1) AS value FROM dual (Oracle)

Ceiling – SELECT CEILING(-1.001) AS value vs SELECT CEIL(-1.001) AS value FROM dual Floor – SELECT FLOOR(-1.001) AS value vs SELECT FLOOR(-1.001) AS value FROM dual Truncate (Cast to int) – SELECT CAST(-1.002 AS int) AS value vs SELECT TRUNC(-1.002) AS value FROM dual Round – SELECT ROUND(1.23456,4) AS value -- 1.23460 vs SELECT ROUND(1.23456,4) AS value FROM dual -- 1.2346 Exponential (e^x) – SELECT EXP(1) AS value -- 2.7182818284590451 vs SELECT EXP(1) AS value FROM dual -- 2.71828182 Natural Logarithm – SELECT LOG(2.7182818284590451) AS value -- 1 vs SELECT LN(2.7182818284590451) AS value FROM dual -- 1 Base‑10 Logarithm – SELECT LOG10(10) AS value -- 1 vs SELECT LOG(10,10) AS value FROM dual -- 1 Square – SELECT SQUARE(4) AS value -- 16 vs SELECT POWER(4,2) AS value FROM dual -- 16 Square Root – SELECT SQRT(4) AS value -- 2 vs SELECT SQRT(4) AS value FROM dual -- 2 Power – SELECT POWER(3,4) AS value -- 81 vs SELECT POWER(3,4) AS value FROM dual -- 81 Random Number – SELECT RAND() AS value vs SELECT SYS.DBMS_RANDOM.VALUE(0,1) AS value FROM dual Sign – SELECT SIGN(-8) AS value -- -1 vs SELECT SIGN(-8) AS value FROM dual -- -1 Pi – SELECT PI() AS value -- 3.1415926535897931 vs -- not available in Oracle Trigonometric Functions (sin, cos, tan) – arguments are in radians; e.g., SELECT SIN(PI()/2) AS value -- 1 (SQL Server) Inverse Trigonometric Functions (ASIN, ACOS, ATAN, ATAN2) – return radians.

Degree ↔ Radian Conversion – DEGREES(radians) converts to degrees; RADIANS(degrees) converts to radians (SQL Server only).

Aggregate and Comparison Functions

Maximum Value in a Set –

SELECT MAX(value) FROM (SELECT 1 AS value UNION SELECT -2 UNION SELECT 4 UNION SELECT 3) AS t

vs SELECT GREATEST(1,-2,4,3) AS value FROM dual Minimum Value in a Set – similar SQL Server query vs SELECT LEAST(1,-2,4,3) AS value FROM dual NULL Handling – SELECT ISNULL(col,10) AS value FROM Tbl vs

SELECT NVL(col,10) AS value FROM Tbl

String Functions

ASCII Code – SELECT ASCII('a') AS value vs SELECT ASCII('a') AS value FROM dual Character from Code – SELECT CHAR(97) AS value vs SELECT CHR(97) AS value FROM dual Concatenation – SELECT '11'+'22'+'33' AS value vs SELECT CONCAT('11','22')||'33' AS value FROM dual Substring Position – SELECT CHARINDEX('s','sdsq',2) AS value -- 3 vs SELECT INSTR('sdsq','s',2) AS value FROM dual Pattern Search (LIKE) – SQL Server uses PATINDEX('%d%q%','sdsfasdqe'); Oracle can achieve similar results with INSTR('sdsfasdqe','sd',1,2).

Substring Extraction – SELECT SUBSTRING('abcd',2,2) AS value vs SELECT SUBSTR('abcd',2,2) AS value FROM dual Replace Substring – SELECT STUFF('abcdef',2,3,'ijklmn') AS value vs SELECT REPLACE('abcdef','bcd','ijklmn') AS value FROM dual Translate Characters – Oracle example:

SELECT TRANSLATE('fasdbfasegas','fa','我') AS value FROM dual

Length – SQL Server: LEN(col) or DATELENGTH(col); Oracle: LENGTH(col) Case Conversion – LOWER(col) / UPPER(col) work in both systems.

Initcap (Title Case) – Oracle: SELECT INITCAP('abcd dsaf df') AS value FROM dual Padding – Left pad: SELECT SPACE(10)+'abcd' AS value vs SELECT LPAD('abcd',14) AS value FROM dual; Right pad: SELECT 'abcd'+SPACE(10) AS value vs SELECT RPAD('abcd',14) AS value FROM dual Trim Spaces – LTRIM, RTRIM, TRIM are available in both.

Repeat String – SQL Server: SELECT REPLICATE('abcd',2) AS value; Oracle does not have a direct equivalent.

Soundex – SELECT SOUNDEX('Smith') AS value works in both; SQL Server also provides DIFFERENCE('Smithers','Smythers') returning a similarity score 0‑4.

Date and Time Functions

Current Date/Time – SELECT GETDATE() AS value vs SELECT SYSDATE AS value FROM dual Adding/Subtracting Days – Direct arithmetic with integers works in both systems.

Extract Date Part – SQL Server uses DATEPART / DATENAME; Oracle uses TO_CHAR(date, 'format') where format strings such as YYYY, MM, DD, etc., specify the part.

Last Day of Month – Oracle: SELECT LAST_DAY(SYSDATE) AS value FROM dual Next Specific Weekday – Oracle: SELECT NEXT_DAY(SYSDATE,7) AS value FROM dual String to Date – Oracle:

SELECT TO_DATE('2004-01-05 22:09:38','YYYY-MM-DD HH24:MI:SS') AS value FROM dual

Date Difference (seconds) – SQL Server: SELECT DATEDIFF(ss,GETDATE(),GETDATE()+12.3) AS value; Oracle: SELECT (d1 - d2) * 24 * 60 * 60 AS value FROM dual Add Minutes – SQL Server: SELECT DATEADD(mi,8,GETDATE()) AS value; Oracle: SELECT SYSDATE + 8/60/24 AS value FROM dual Time Zone Conversion – Oracle: SELECT NEW_TIME(SYSDATE,'YDT','GMT') AS value FROM dual (YDT = Beijing UTC+8). Various abbreviations such as AST, BST, CST, EST, GMT, HST, MST, NST, PST, YST are listed.

Cross‑Database Function Reference Table

Character Functions

ASCII – Oracle: ASCII, SQL Server: ASCII

Concatenation – Oracle: CONCAT(... ) || ..., SQL Server: expression + expression

CHR/CHAR – Oracle: CHR, SQL Server: CHAR

INSTR/CHARINDEX – Oracle: INSTR, SQL Server: CHARINDEX

LOWER – both

UPPER – both

LPAD – Oracle only

LTRIM/RTRIM – both

PATINDEX – SQL Server only; Oracle can use INSTR with additional parameters

REPLICATE – SQL Server only; Oracle can use RPAD for spaces

SOUNDEX – both

TRANSLATE – Oracle only

LENGTH – Oracle only; SQL Server uses LEN/DATALENGTH

INITCAP – Oracle only

Date Functions

Current date/time – SYSDATE (Oracle) / GETDATE() (SQL Server)

ADD_MONTHS / DATEADD – add months

MONTHS_BETWEEN / DATEDIFF – difference in months

LAST_DAY – Oracle only

NEW_TIME – Oracle time‑zone conversion

NEXT_DAY – Oracle next weekday

TO_CHAR / DATENAME – format date parts

ROUND / CONVERT – date rounding

TRUNC / CONVERT – date truncation

TO_DATE / CONVERT – string to date

NVL / ISNULL – null handling

Conversion Functions

TO_CHAR / CONVERT – number to string

TO_NUMBER / CONVERT – string to number

HEX_TO_RAW / CONVERT – hex to binary (Oracle only)

RAW_TO_HEX / CONVERT – binary to hex (Oracle only)

Miscellaneous Functions

DECODE / COALESCE – first non‑null expression

CURRVAL / NEXTVAL – sequence values (Oracle)

NULLIF – return null when two expressions are equal

USER‑related functions – SUSER_ID, SUSER_NAME (SQL Server) vs UID, USER, CURRENT_USER (Oracle)

Aggregate Functions

AVG – average

COUNT – count

MAX – maximum

MIN – minimum

STDDEV – standard deviation

SUM – summation

VARIANCE – variance

EXTRACT – Oracle function to retrieve specific date/time fields