|
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).
Numbers in parentheses refer to notes below the table.
Convertible SQL Functions
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 (2)
|
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 (3)
|
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 (2)
|
CHARINDEX
|
CHARINDEX
|
LOCATE
|
ISNULL
|
NVL
|
ISNULL
|
ISNULL
|
IFNULL
|
LCASE
|
LOWER
|
LOWER
|
LOWER
|
LCASE
|
LEFT
|
-
|
-
|
-
|
LEFT
|
LENGTH
|
LENGTH
|
DATALENGTH
|
LEN (3)
|
LENGTH
|
LENGTHB
|
LENGTH
|
DATALENGTH
|
DATALENGTH
|
LENGTH
|
LN
|
LN
|
LOG
|
LOG
|
LOG
|
LOCATE
|
INSTR (2)
|
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 (2)
|
SUBSTRING (2)
|
SUBSTRING (2)
|
SUBSTRING
|
SUBSTRB
|
SUBSTR
|
SUBSTRING
|
SUBSTRING
|
SUBSTRING
|
SUBSTRING
|
SUBSTR (2)
|
SUBSTRING (2)
|
SUBSTRING (2)
|
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
|
| (1) | CHR(0) is the end-of-string character. When using CHR(0) in USoft within a concatenated string (CHR(0)||'some text'), everything following the CHR(0) character will NOT be read. |
| 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. |
| (2) | When the SUBSTR and INSTR Oracle functions are converted to Sybase, please note that: |
| For SUBSTR, if the number of parameters is less than three, a third parameter will be added for Sybase: length (parameter1). |
| For INSTR, if the number of parameters is more than two, the third parameter (and higher) will be truncated when used on Sybase. |
| (3) | On SQL Server, the DATALENGTH(expression) function returns the number of bytes used to represent the expression. The LEN function returns the number of characters of the expression, excluding trailing blanks. 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. |
|
