Convertible SQL Functions

Convertible functions are native functions in the RDBMS you develop your application in. If the application is ported to another database platform, convertible functions are automatically converted to the equivalent format of the target database.

Use functions that are native to your RDBMS, referring to your database vendor's documentation on SQL functions. If you wish to develop a portable application, make sure you only use functions listed below as being convertible to your target RDBMS(s).

If a column contains a dash ( - ) then the function is NOT convertible to that particular database.

For example, the GETDATE Sybase function will be converted to Oracle (using Oracle's SYSDATE) or to ODBC (using ODBC's NOW).

Alphabetic Order	Oracle	Sybase	MS SQL Server	ODBC SOLID
ABS	ABS	ABS	ABS	ABS
ACOS	-	ACOS	ACOS	ACOS
ASCII	ASCII	ASCII	ASCII	ASCII
ASIN	-	ASIN	ASIN	ASIN
ATAN	-	ATAN	ATAN	ATAN
CEIL	CEIL	CEILING	CEILING	CEILING
CEILING	CEIL	CEILING	CEILING	CEILING
CHARINDEX	INSTR (3)	CHARINDEX	CHARINDEX	LOCATE
CHAR_TO_DATE	TO_DATE	CONVERT	CONVERT	CAST
CHR (1)	CHR (1)	CHAR (1)	CHAR (1)	CHAR (1)
CONCAT	CONCAT	-	-	CONCAT
COS	COS	COS	COS	COS
COT	-	COT	COT	COT
CURRENT	SYSDATE	GETDATE	GETDATE	NOW
DATALENGTH	LENGTH	DATALENGTH	DATALENGTH (4)	LENGTH
DATE	-	-	-	-
DATE_TO_CHAR	TO_CHAR	CONVERT	CONVERT	CAST
DECODE	DECODE	CASE	CASE	CASE
DEGREES	-	DEGREES	DEGREES	DEGREES
DIFFERENCE	-	DIFFERENCE	DIFFERENCE	DIFFERENCE
EXP	EXP	EXP	EXP	EXP
FLOOR	FLOOR	FLOOR	FLOOR	FLOOR
GETDATE	SYSDATE	GETDATE	GETDATE	NOW
IFNULL	NVL	ISNULL	ISNULL	IFNULL
INITCAP	INITCAP	-	-	-
INSTR	INSTR (3)	CHARINDEX	CHARINDEX	LOCATE
ISNULL	NVL	ISNULL	ISNULL	IFNULL
LCASE	LOWER	LOWER	LOWER	LCASE
LEFT	-	-	-	LEFT
LENGTH	LENGTH	DATALENGTH	LEN (4)	LENGTH
LENGTHB	LENGTH	DATALENGTH	DATALENGTH	LENGTH
LN	LN	LOG	LOG	LOG
LOCATE	INSTR (3)	CHARINDEX	CHARINDEX	LOCATE
LOG	LN	LOG	LOG	LOG
LOG10	-	LOG10	LOG10	LOG10
LOWER	LOWER	LOWER	LOWER	LCASE
LPAD	LPAD	-	-	-
MOD	MOD	MOD	MOD	MOD
MONTH	-	-	-	-
NOW	SYSDATE	GETDATE	GETDATE	NOW
NVL	NVL	ISNULL	ISNULL	IFNULL
PI	-	PI	PI	PI
POWER	POWER	POWER	POWER	POWER
RADIANS	-	RADIANS	RADIANS	RADIANS
RAND	-	RAND	RAND	RAND
RIGHT	-	RIGHT	RIGHT	RIGHT
ROUND	ROUND	ROUND	ROUND	ROUND
REPLACE	REPLACE	REPLACE	REPLACE	-
RPAD	RPAD	-	-	-
SIGN	SIGN	SIGN	SIGN	SIGN
SIN	SIN	SIN	SIN	SIN
SOUNDEX	SOUNDEX	SOUNDEX	SOUNDEX	SOUNDEX
SPACE	-	SPACE	SPACE	SPACE
SQRT	SQRT	SQRT	SQRT	SQRT
SUBSTR	SUBSTR (3)	SUBSTRING (3)	SUBSTRING (3)	SUBSTRING
SUBSTRB	SUBSTR	SUBSTRING	SUBSTRING	SUBSTRING
SUBSTRING	SUBSTR (3)	SUBSTRING (3)	SUBSTRING (3)	SUBSTRING
SYSDATE	SYSDATE	GETDATE	GETDATE	NOW
TAN	TAN	TAN	TAN	TAN
TO_INTEGER	TO_NUMBER	CONVERT (INTEGER)	CONVERT (INTEGER)	CONVERT
UCASE	UPPER	UPPER	UPPER	UCASE
UID	UID	USER_ID	USER_ID	-
UPPER	UPPER	UPPER	UPPER	UCASE
USER	USER	USER_NAME	USER_NAME	USER
USER_ID	UID	USER_ID	USER_ID	-
USER_NAME	USER	USER_NAME	USER_NAME	USER
YEAR	-	-	-	YEAR

(2) The function can be handled by the local evaluator, but not for statements that will be sent to the RDBMS. Because many context-bound factors help decide if a statement is sent to the RDBMS, successful function handling is not guaranteed.

(3) When the SUBSTR and INSTR Oracle functions are converted to Sybase, please note that:

This LEN function is not supported on text (and ntext) fields. Use the DATALENGTH function instead, taking into account that this returns the number of bytes instead of the number of characters.