oracle sybase 비교

 

아직도 sybase 를 쓰는 곳이 있다고 한다.

 

외국 사이트를 돌아다니다 우연히 발견한 oracle 과 sybase 를 비교해논 표를 퍼왔다.

 

oracle 은 비교적 많은 곳에서 사용하고 기본적인 부분을 다룰 줄 아는 사람도 많다.

 

sybase 를 접하게 되면 대부분 처음 접하는 것이라 예상한다. 그렇기 때문에 간단한 함수조차 알지 못해 난항을 겪게 될 것이다.

 

 

그나마 익숙한 oracle 의 함수, 타입, 사용방법 등이 sybase 에서는 어떤 명칭으로 어떻게 정의되고 사용되는지 등을 비교하면 보다 쉽게 sybase 를 활용할 수 있을 듯 하다.

 

비교 대상은 oracle 10g, sybase 15.0.1 이다.

 

Sybase vs Oracle
Sybase 15.0.1	Oracle 10gR2
Verbiage
Instance	Database
	Instance
Database	Schema
User	User
System & User Databases	Schemas
master	SYS and SYSTEM schemas
model	SYS and SYSTEM schemas
sybsyntax	SYS and SYSTEM schemas
sybsystemdb	SYS and SYSTEM schemas
sybsystemprocs	SYS and SYSTEM schemas
tempdb	temporary tablespace
no equivalent ... uses its log files	undo (rollback) segment
Storage Concepts
Row	Row
no equivalent	Undo
Page	Block
Extent	Extent
Segment	Segment
Datafile	Datafile
Tempfile	Tempfile
DBSpace	Tablespace
no equivalent	Default Tablespace
Note: The the way primary and secondary datafiles are mapped in SQL Server does not relate to how data files are mapped in Oracle.
no equivalent	Real Application Cluster
Companion Mode	Data Guard
Suspended Mode	Standby Server
BCP	SQL*Loader
Primary Data File	System Data Files
Secondary Data File	Data File
Log File	Log File
Note: The log file concept and architecture are completely different
Truncate Transaction Logs	Log Files Not Used For Rollback
Object Types
Clusters
no equivalent	Cluster by Hash
no equivalent	Cluster by Index
no equivalent	Sorted Hash Cluster
Constraints
Primary Key	Primary Key
Unique Key	Unique
Foreign Key	Referential (Foreign Key)
no equivalent	Referential ON DELETE CASCADE
no equivalent	Referential ON DELETE SET NULL
no equivalent	Deferrable
no equivalent	Rely / Norely
no equivalent	Validate / Novalidate
Check & Rule	Check
Null / Not Null	Null / Not Null
no equivalent	Read Only
no equivalent	REF (Nested Table Constraint)
Table Level Constraints	no equivalent
Database Linkages
Linked Server	Database (DB) Link
Dimension
Dimension	Dimension
Functions (user defined)
Function	Function
no equivalent	Pipelined Table Function
IN and OUT Parameters Only	IN, OUT, and IN-OUT Parameters
Parameter Default	Parameter Default
Indexes
Index	B*Tree
no equivalent	Bitmap
Bitmap Join	Bitmap Join
no equivalent	Cluster
Clustered Index	Index Organized Table
no equivalent	Compressed
no equivalent	Descending
Function Based	Function Based
Global	Global
Local	Local
no equivalent	No Segment (Virtual)
Non-Unique	Non-Unique
no equivalent	Reverse
no equivalent	REF
Unique	Unique
Libraries
Assembly	Library
Materialized Views
no equivalent	Materialized Views of any type
Operators (user defined)
no equivalent	Operator
no equivalent	Overloading
Packages
no equivalent	Package Header
no equivalent	Package Body
no equivalent	Initialization Section
no equivalent	Overloading
no equivalent	Serial Reusability
Procedures (user defined)
Procedure	Stored Procedure
IN and OUT Parameters Only	IN, OUT, and IN-OUT Parameters
Parameter Default	Parameter Default
no equivalent	NOCOPY
no equivalent	AUTHID
Schemas
Schema	Schema
Surrogate Key Generator
no equivalent	Sequence
Identity & NEWID	no equivalent
Synonyms
no equivalent	Private Synonym
no equivalent	Public Synonym
Tables
Table	Heap Table
Cluster Index	Index Organized Table
Column Default	Column Default
no equivalent	Compressed Table
Computed Column	Function Based Index
no equivalent	External Table
no equivalent	Global Temporary Table
	(con commit delete rows)
no equivalent	Global Temporary Table
	(on commit preserve rows)
no equivalent	Nested Table
Partition	not relevant due to architecture
no equivalent	Hash Partitioned
no equivalent	List Partitioned Table
no equivalent	Range Partitioned Table
no equivalent	Subpartitioned Table
Temporary Table	not relevant due to MVCC
no equivalent	XML Table
Types
Type	Type
no equivalent	Type Header
no equivalent	Type Body with Methods
no equivalent	Object
no equivalent	VArray
Views
View	View
Check Option	Check Option
no equivalent	ORDER BY
Built-in Operators
Arithmetic Operators
+ (add)	+ (add)
- (subtract)	- (subtract)
* (multiply)	* (multiply)
/ (divide)	/ (divide)
% (modulo)	mod function
power function	** (power)
Assignment Operators
= (equals)	:= (colon equals)
Bitwise Operators
& (bitwise AND)	utl_raw.bit_and
| (bitwise OR)	utl_raw.bit_or
^ (bitwise exclusive OR)	utl_raw.bit_xor
~ (bitwise NOT)	no equivalent
no equivalent	utl_raw.bit_complement
Comparison Operators
= (equal to)	= (equal to)
> (greater than)	> (greater than)
< (less than)	< (less than)
>= (greater than or equal to)	>= (greater than or equal to)
<= (less than or equal to)	<= (less than or equal to)
<> (not equal to)	<> (not equal to)
!= (not equal to)	!= (not equal to)
!= (not equal to)	=^ (not equal to)
!= (not equal to)	~= (not equal to)
!< (not less than)	no equivalent
!> (not greater than)	no equivalent
Date Operators
no equivalent	+ (add)
no equivalent	- (subtract)
Hierarchical Operators
no equivalent	CONNECT
no equivalent	CONNECT BY
no equivalent	CONNECT BY PRIOR
no equivalent	CONNECT BY ROOT
Conditions
ALL	ALL
AND	AND
ANY	ANY
BETWEEN	BETWEEN
CONTAINS	Context Operator
no equivalent	DEPTH
no equivalent	EMPTY
no equivalent	EQUALS_PATH
EXISTS	EXISTS
IN	INFINITE
no equivalent	IS A SET
no equivalent	IS ANY
IS NULL	IS NULL
no equivalent	IS OF ONLY
no equivalent	IS OF TYPE
no equivalent	IS PRESENT
LIKE	LIKE
no equivalent	MEMBER OF
no equivalent	NAN
NOT	NOT
OR	OR
no equivalent	PATH
no equivalent	SOME
no equivalent	SUBMULTISET OF
no equivalent	UNDER_PATH
Multiset Operators
no equivalent	MULTISET
no equivalent	MULTISET EXCEPT
no equivalent	MULTISET INTERSECT
no equivalent	MULTISET UNION
Set Operators
INTERSECT	INTERSECT
no equivalent	MINUS
MERGE_UNION_ALL	no equivalent
UNION	UNION
UNION ALL	UNION ALL
String Operators
+ (concatenation)	|| (concatenation)
Unary Operators
+ (positive)	+ (positive)
- (negative)	- (negative)
Triggers
DDL Triggers
no equivalent	DATABASE
no equivalent	ALTER
no equivalent	ASSOCIATE STATISTICS
no equivalent	AUDIT
no equivalent	COMMENT
no equivalent	CREATE
no equivalent	SCHEMA
no equivalent	(not irrelevant)
no equivalent	DDL
no equivalent	DISASSOCIATE STATISTICS
no equivalent	DROP
no equivalent	GRANT
no equivalent	NOAUDIT
no equivalent	RENAME
no equivalent	REVOKE
no equivalent	SUSPEND
no equivalent	TRUNCATE
no equivalent	ANALYZE
Instead-Of Triggers
VIEW TRIGGER	INSTEAD-OF TRIGGER
System Triggers
no equivalent	DATABASE
no equivalent	SCHEMA
no equivalent	AFTER LOGON
no equivalent	AFTER SERVERERROR
no equivalent	AFTER STARTUP
no equivalent	BEFORE LOGOFF
no equivalent	BEFORE SHUTDOWN
Table Triggers
no equivalent	before insert statement level
no equivalent	before update statement level
no equivalent	before delete statement level
no equivalent	before insert row level
no equivalent	before update row level
no equivalent	before delete row level
after insert trigger	after insert statement level
after update trigger	after update statement level
after delete trigger	after delete statement level
no equivalent	after insert row level
no equivalent	after update row level
no equivalent	after delete row level
no equivalent	OF Clause
no equivalent	REFERENCING Clause
no equivalent	native compilation and wrap

 

	Sybase 15.0.1	Oracle 10gR2
Functions
Analytic Functions
Running average	no equivalent	AVG
Coefficient of correlation	no equivalent	CORR
Running count by partition	no equivalent	COUNT
Population covariance of a set of pairs	no equivalent	COVAR_COUNT
Sample covariance of a set of pairs	no equivalent	COVAR_SAMP
Cumulative distribution in a group	no equivalent	CUME_DIST
Rank within a group without gaps	DENSE_RANK	DENSE_RANK
Row ranked first by DENSE RANK	no equivalent	FIRST
First value of an ordered set	no equivalent	FIRST_VALUE
Provides access to a row by offset	no equivalent	LAG
Row ranked last by DENSE RANK	no equivalent	LAST_VALUE
Last value of an ordered set	no equivalent	LAST_VALUE
Provides access to a row by offset	no equivalent	LEAD
Maximum value by partition	no equivalent	MAX
Minimum value by partition	no equivalent	MIN
Divides an ordered dataset into buckets	NTILE	NTILE
Rowset partitioning	OVER	OVER
Calculates the value of r-1/rows-1	PERCENT_RANK	PERCENT_RANK
An inverse distribution function	PERCENTILE_CONT	PERCENTILE_CONT
An inverse distribution function	PERCENTILE_DISC	PERCENTILE_DISC
Rank of a value in a group	RANK	RANK
Computes ratio of a value to the sum of a set	no equivalent	RATIO_TO_REPORT
Linear regression function	no equivalent	REGR_AVGX
Linear regression function	no equivalent	REGR_AVGY
Linear regression function	no equivalent	REGR_COUNT
Linear regression function	no equivalent	REGR_INTERCEPT
Linear regression function	no equivalent	REGR_R2
Linear regression function	no equivalent	REGR_SLOPE
Linear regression function	no equivalent	REGR_SXX
Linear regression function	no equivalent	REGR_SXY
Linear regression function	no equivalent	REGR_SYY
Assigns row numbers by partition	no equivalent	ROW_NUMBER
Sample standard deviation	no equivalent	STDDEV
Square root of the population variance	STDDEV_POP	STDDEV_POP
Cumulative sample standard deviation	no equivalent	STDDEV_SAMP
Cumulative running total	no equivalent	SUM
Population variance of a set	VAR_POP	VAR_POP
Sample variance of a set	no equivalent	VAR_SAMP
Variance of an expression	no equivalent	VARIANCE
Collection Functions
Number of elements in a nested table	no equivalent	CARDINALITY
Creates a nested table from selected rows	no equivalent	COLLECT
Creates a nested table of nonempty subsets	no equivalent	POWERMULTISET
As above: Of the specified cardinality	no equivalent	POWERMULTISET_BY_CARDINALITY
Converts a nested table into a unique set	no equivalent	SET
Conversion Functions
ASCII string into the DB character set	no equivalent	ASCIISTR
BFILE from directory + file name	no equivalent	BFILENAME
Integer to hex	BIGINTTOHEX	RAWTOHEX
Bitvector to a number	no equivalent	BIN_TO_NUM
One data type to another	CAST & CONVERT	CAST
String to a ROWID	not relevant	CHARTOROWID
String to a unicode string	no equivalent	COMPOSE
One character set to another	no equivalent	CONVERT
Unicode string to a string	no equivalent	DECOMPOSE
Hex to integer	HEXTOINT	TO_NUMBER
Char containing hexidecimal digits to raw	no equivalent	HEXTORAW
Integer to hex	INTTOHEX	RAWTOHEX
Number into a Day-to-Second interval	no equivalent	NUMTODSINTERVAL
Number into a Year-to-Month interval	no equivalent	NUMTOYMINTERVAL
A value to its hash	no equivalent	ORA_HASH
Raw into a hexidecimal containing string	no equivalent	RAWTOHEX
Raw into a hexidecimal containing 'N' string	no equivalent	RAWTONHEX
Raw into a hexidecimal object REF	no equivalent	REFTOHEX
ROWID to CHAR	not relevant	ROWIDTOCHAR
ROWID to NCHAR	not relevant	ROWIDTONCHAR
Timestamp to its SCN equivalent	not relevant	SCNTOTIMESTAMP
Converts RAW to NUMBER	no equivalent	SYS_OP_RAWTONUM
An SCN to its timestamp equivalent	not relevant	TIMESTAMPTOSCN
Convert to BINARY_DOUBLE data type	no equivalent	TO_BINARYDOUBLE
Convert to BINARY_FLOAT data type	no equivalent	TO_BINARYFLOAT
Convert to CHAR or VARCHAR2 data type	DATEFORMAT & STR	TO_CHAR
Convert to CLOB data type	no equivalent	TO_CLOB
Convert to DATE data type	DATE (more limited)	TO_DATE
Convert to Day-to-Second Interval data type	no equivalent	TO_DSINTERVAL
LONG or LONG RAW to LOB data type	no equivalent	TO_LOB
Single byte to corresponding multi-byte	no equivalent	TO_MULTI_BYTE
Convert to NCHAR data type	no equivalent	TO_NCHAR
Convert to NCLOB data type	no equivalent	TO_NCLOB
Convert to NUMBER data type	CONVERT	TO_NUMBER
Multi-byte to corresponding single byte	no equivalent	TO_SINGLE_BYTE
Convert to TIMESTAMP data type	DATETIME (more limited)	TO_TIMESTAMP
To TIMESTAMP WITH TIMEZONE data type	no equivalent	TO_TIMESTAMP_TZ
Convert to Year-to-Month Interval data type	no equivalent	TO_YMINTERVAL
Changes character set	no equivalent	TRANSLATE_USING
String to UTF8 or UTF16	no equivalent	UNISTR
Convert values to a date	YMD	TO_DATE
Date-Time Functions
Date addition	DATEADD & DAYS	+
Date subtraction	DATEDIFF & DAYS	-
Add a month	no equivalent	ADD_MONTHS
First non-null value	COALESCE	COALESCE
Current date and time (low precision)	GETDATE	CURRENT_DATE
Current date and time (high precision)	no equivalent	CURRENT_TIMESTAMP
Current date and time (low second)	NOW & TODAY	SYSDATE
Hours since a starting date and time	HOURS	no equivalent
Minutes since a starting date and time	MINUTES	no equivalent
Seconds since a starting date and time	SECONDS	no equivalent
Weeks since a starting date and time	WEEKS	no equivalent
Years since a starting date and time	YEARS	no equivalent
Current date and time (high precision)	no equivalent	SYSTIMESTAMP
Database's time-zone	no equivalent	DBTIMEZONE
Extract part from date-time or interval	no equivalent	EXTRACT
Alter time zone information	no equivalent	FROM_TZ
Largest of a set of dates	no equivalent	GREATEST
Last day of month	no equivalent	LAST_DAY
Smallest of a set of dates	no equivalent	LEAST
Months between dates	MONTHS	MONTHS_BETWEEN
Time zone conversion	no equivalent	NEW_TIME
First weekday after date	no equivalent	NEXT_DAY
Quarter of the calendar year	QUARTER	no equivalent
Rounds date to unit specified	no equivalent	ROUND
Current session's time zone	no equivalent	SESSIONTIMEZONE
Coordinated universal time	GETUTCDATE	SYS_EXTRACT_UTC
Convert date part to name	DATENAME	TO_CHAR
Convert date part to number	DATEPART & DOW	TO_CHAR
Name of the day of the week	DATENAME	TO_CHAR
Integer representing the day of the week	DAY	TO_CHAR
Integer representing the hour	HOUR	TO_CHAR
Integer representing the minute	MINUTE	TO_CHAR
Integer representing the month of the year	MONTH	TO_CHAR
Name of the month of the year	MONTHNAME	TO_CHAR
Integer representing the seconds	SECOND	TO_CHAR
Integer representing the year	YEAR	TO_CHAR
Convert string to date	CAST	TO_DATE
Determine if a value/expression is a valid date	ISDATE	TO_DATE
Date from date-time	no equivalent	TRUNC
Time-zone offset	no equivalent	TZ_OFFSET
Data Mining Functions
Cluster ID of the cluster with highest probability	no equivalent	CLUSTER_ID
Degree of confidence of membership of a row	no equivalent	CLUSTER_PROBABILITY
Varray of objects of possible clusters	no equivalent	CLUSTER_SET
Feature ID with highest coefficient value	no equivalent	FEATURE_ID
Varray of objects of all possible features	no equivalent	FEATURE_SET
Value of a given feature	no equivalent	FEATURE_VALUE
Best prediction for the specified model	no equivalent	PREDICTION
Cost measure of a given prediction	no equivalent	PREDICTION_COST
XML with model specific scoring	no equivalent	PREDICTION_DETAILS
Probability for a given prediction	no equivalent	PREDICTION_PROBABILITY
Varray of objects with all possible classes	no equivalent	PREDICTION_SET
Environment Functions
Database / Schema Identifier	DB_ID	SYS_CONTEXT
Database Name	DB_NAME	SYS_CONTEXT
Host Identifier	HOST_ID	SYS_CONTEXT
Workstation Name	HOST_NAME	SYS_CONTEXT
Local language identifier	@@LANGID	SYS_CONTEXT
Name of language in use	@@LANGUAGE	SYS_CONTEXT
Value with the named context namespace	no equivalent	SYS_CONTEXT
User Session ID	@@SPID	UID
User name	CURRENT_USER	USER
Schema ID	SCHEMA_ID	SYS_CONTEXT
Schema name	SCHEMA_NAME	SYS_CONTEXT
Username in the current context	SESSION_USER	SYS_CONTEXT
Value with the named context namespace	no equivalent	USERENV
Miscellaneous Functions
Null BLOB	no equivalent	EMPTY_BLOB
Null CLOB	no equivalent	EMPTY_CLOB
Length of an NCHAR column	no equivalent	NLS_CHARSET_DECL_LEN
ID of NLS character set	no equivalent	NLS_CHARSET_ID
Name of NLS character set from ID	no equivalent	NLS_CHARSET_NAME
Numeric identifier of current exception code	SQLCODE	SQLCODE
Error message of current exception code	no equivalent	SQLERRM
Hierarchical path of column from root to node	no equivalent	SYS_CONNECT_BY_PATH
16 byte GUID	no equivalent	SYS_GUID
Function that builds descending index values	no equivalent	SYS_OP_DESCEND
Index leaf block ID scan	no equivalent	SYS_OP_LBID
Model Functions
Use left side value on right side calculation	no equivalent	CV
Iterate through data	no equivalent	ITERATE
Iterate a set number of times through data	no equivalent	ITERATE_UNTIL
Current iteration number	no equivalent	ITERATION_NUMBER
Returns expr1 prior to execution	no equivalent	PRESENTNNV
Returns expr1 prior to execution	no equivalent	PRESENTV
Reference prior model values	no equivalent	PREVIOUS
Null Handling Functions
Evaluate one or both operands may be NULL	no equivalent	LNNVL
Returns NULL is expr1 and expr2 both NULL	NULLIF	NULLIF
Convert to string if NULL	ISNULL	NVL
Substitute if NULL or if NOT NULL	IFNULL	NVL2
Map NULL for joins	(not relevant)	SYS_OP_MAP_NONNULL
Numeric Handling Functions
Absolute value	ABS	ABS
Arc cosine	ACOS	ACOS
Arc sine	ASIN	ASIN
Arc tangent of n	ATAN	ATAN
Arc tangent1 divided by the arc tangent2	ATN2	ATAN2
Average	AVG	AVG
Compute AND operation on bits	no equivalent	BITAND
Smallest integer >= value	CEILING	CEIL
First non-null value	COALESCE	COALESCE
Coefficient of correlation	no equivalent	CORR
Pearson's coefficient of correlation	no equivalent	CORR_K
Spearman's Rho correlation coefficient	no equivalent	CORR_S
Cosine	COS	COS
Hyperbolic cosine	COT	COSH
Number of values	COUNT	COUNT
Number of non-null values	COUNT_BIG	COUNT
Population covariance	no equivalent	COVAR_POP
Sample covariance	no equivalent	COVAR_SAMP
Cumulative Distribution	no equivalent	CUME_DIST
Degrees	DEGREES	no equivalent
Rank of row in an ordered group	no equivalent	DENSE_RANK
Exponential value	EXP	EXP
Row ranked first using DENSE_RANK	no equivalent	FIRST
Round down to nearest integer	FLOOR	FLOOR
Largest of multiple values	no equivalent	GREATEST
Tests if numeric conversion will work	ISNUMERIC	TO_NUMBER
Row ranked last using DENSE_RANK	no equivalent	LAST
Smallest of multiple values	no equivalent	LEAST
Natural logarithm	LOG	LN
Logarithm, base 10	LOG10	LOG
Maximum returned value	MAX	MAX
Middle value of the set	MEDIAN	MEDIAN
Minimum returned value	MIN	MIN
Remainder from modulus using floor	MOD	MOD
Returns alternate number if value not a number	no equivalent	NANVL
Percent ranking	no equivalent	PERCENT_RANK
Inverse distribution continuous dist. model	no equivalent	PERCENTILE_CONT
Inverse distribution discrete distribution model	no equivalent	PERCENTILE_DISC
Returns the value of Pi	PI	no equivalent
Raise value to exponent power	POWER	POWER
Radians from a numeric expression	RADIANS	no equivalent
Random Number	RAND	dbms_cryto package
Rank in a group	no equivalent	RANK
Linear regression - avg of the independent var.	no equivalent	REGR_AVGX
Linear regression - avg of the independent var.	no equivalent	REGR_AVGY
Linear regression - non-null number pairs	no equivalent	REGR_COUNT
Linear regression - y intercept	no equivalent	REGR_INTERCEPT
Linear regression - coefficient of determination	no equivalent	REGR_R2
Linear regression - slope of the line	no equivalent	REGR_SLOPE
Linear regression - auxiliary function	no equivalent	REGR_SXX
Linear regression - auxiliary function	no equivalent	REGR_SXY
Linear regression - auxiliary function	no equivalent	REGR_SYY
Remainder from modulus using round	REMAINDER	REMAINDER
Round to integer place	ROUND	ROUND
Sign of number	SIGN	SIGN
Sine	SIN	SIN
Hyperbolic sine	no equivalent	SINH
Square	SQUARE	no equivalent
Square root	SQRT	SQRT
Exact probability test for dichotomous variables	no equivalent	STATS_BINOMIAL_TEST
Crosstabulation analysis of nominal variables	no equivalent	STATS_CROSSTAB
Whether two values are significantly different	no equivalent	STATS_F_TEST
Kolmogorov-Smirnov function	no equivalent	STATS_KS_TEST
Value with the greatest frequency	no equivalent	STATS_MODE
Mann Whitney test	no equivalent	STATS_MW_TEST
One-way analysis of variance function	no equivalent	STATS_ONE_WAY_ANOVA
measures significance of a difference of means	no equivalent	STATS_T_TEST
Wilcoxon Signed Ranks test of paired samples	no equivalent	STATS_WSR_TEST
Standard deviation	STDDEV	STDDEV
Square root of the population variance	STDDEV_POP	STDDEV_POP
Cumulative sample standard deviation	STDDEV_SAMP	STDDEV_SAMP
Summation	SUM	SUM
Tangent	TAN	TAN
Hyperbolic tangent	no equivalent	TANH
Truncates to specified decimal places	TRUNCATE & TRUNCNUM	TRUNC
Population variance of a set	VAR_POP	VAR_POP
Sample variance of a set	VAR_SAMP	VAR_SAMP
Variance of an expression	VARIANCE	VARIANCE
Construct equiwidth histograms	WIDTH_BUCKET	WIDTH_BUCKET
Object Functions
Object reference of an argument	no equivalent	DEREF
Creates a REF to an object row	no equivalent	MAKEREF
Returns a REF of an object instance	no equivalent	REF
Typeid of the most specific type of the operand	no equivalent	SYS_TYPEID
Returns object instance from an object table	no equivalent	VALUE
String Handling Functions
Get the ASCII value of a character	ASCII	ASCII
Convert ASCII to character	CHAR	CHR
First non-null value	COALESCE	COALESCE
Concatenate strings	STRING	CONCAT
Converts From One Character Set To Another	no equivalent	CONVERT
Capitalize first letter of each word in string	no equivalent	INITCAP
Inserts a string into another string	INSERTSTR	no equivalent
Starting point of pattern in a string	CHARINDEX, LOCATE & PATINDEX	INSTR
Starting point in bytes of pattern in a string	no equivalent	INSTRB
Starting point in Unicode of pattern in a string	no equivalent	INSTRC
Starting point in UCS2 of pattern in a string	no equivalent	INSTR2
Starting point in UCS4 of pattern in a string	no equivalent	INSTR4
Length of character string in characters	BYTE_LENGTH, CHAR_LENGTH &	LENGTH
	LENGTH
Length of character string in bytes	no equivalent	LENGTHB
Convert characters to lower case	LCASE & LOWER	LOWER
Pad left side of character string	SPACE	LPAD
Left trim a string	LTRIM	LTRIM
NLS initial letter upper case	no equivalent	NLS_INITCAP
NLS lower case	no equivalent	NLS_LOWER
String of bytes used to sort a string	no equivalent	NLSSORT
NLS upper case	no equivalent	NLS_UPPER
Define quote delimiters	no equivalent	QUOTE_DELIMITERS
Regular expression instring	no equivalent	REGEXP_INSTR
Regular expression replace	no equivalent	REGEXP_REPLACE
Regular expression substring	no equivalent	REGEXP_SUBSTR
Concatenates a specified number of times	REPEAT & REPLICATE	no equivalent
Replace part of a string with a string	REPLACE, STR_REPLACE	REPLACE
Reverses a character expression	REVERSE	REVERSE
Similarity between two strings	SIMILAR	UTL_MATCH built-in Package
Pad right side of character string	SPACE	RPAD
Right trim a string	RTRIM	RTRIM
Phonetic representation of character string	SOUNDEX	SOUNDEX
String replacing a number of characters	STUFF	no equivalent
String Matching	DIFFERENCE	UTL_MATCH built-in Package
Substring in characters	LEFT, RIGHT & SUBSTRING	SUBSTR
Substring in bytes	no equivalent	SUBSTRB
Substring in Unicode characters	no equivalent	SUBSTRC
Substring in UCS2	no equivalent	SUBSTR2
Substring in UCS4	no equivalent	SUBSTR4
Translate character string	no equivalent	TRANSLATE
Translate character string using character set	no equivalent	TRANSLATE USING
Change declared type of an expression	no equivalent	TREAT
Left and right trim a string	TRIM	TRIM
Convert characters to upper case	UCASE & UPPER	UPPER
XML Handling Functions
Append value to target XML as a child node	no equivalent	APPENDCHILDXML
Deletes node(s) matched by XPath expression	no equivalent	DELETEXML
Levels in the path specified by UNDER_PATH	no equivalent	DEPTH
Does specified node exist	no equivalent	EXISTSNODE
Returns XMLType instance containing fragment	no equivalent	EXTRACT
Returns a scalar value of the resultant node	no equivalent	EXTRACTVALUE
Inserts value to target XML as a child node	no equivalent	INSERTCHILDXML
Inserts value to target XML before named node	no equivalent	INSERTXMLBEFORE
Relative path that leads to resource	no equivalent	PATH
Generates a URL of datatype DBURIType	no equivalent	SYS_DBURIGEN
Aggregates XML documents or fragments	no equivalent	SYS_XMLAGG
Returns XMLType containing an XML doc.	no equivalent	SYS_XMLGEN
Returns XMLType instance with updated value	no equivalent	UPDATEXML
Returns an aggregated XML document	no equivalent	XMLAGG
Generates a CDATA section	no equivalent	XMLCDATA
Creates XML fragment & expands resulting XML	no equivalent	XMLCOLLATVAL
Generates an XML comment	no equivalent	XMLCOMMENT
Concatenates XML elements	no equivalent	XMLCONCAT
Returns concatenation of XML fragments	no equivalent	XMLFOREST
Parses and generates an XML instance	no equivalent	XMLPARSE
Generates an XML processing instruction	no equivalent	XMLPI
Returns query results as XML	no equivalent	XMLQUERY
Create new XML value from version & properties	no equivalent	XMLROOT
Returns Varray of top level nodes	no equivalent	XMLSEQUENCE
Creates a string/LOB containing the contents	no equivalent	XMLSERIALIZE
Returns query of XML results as relational data	no equivalent	XMLTABLE
Applies XSLT to XML instance	no equivalent	XMLTRANSFORM
Data Types
String (Character) Types
Fixed length string	CHAR (8K)	CHAR (2K)
Fixed length string	NCHAR (8K)	NCHAR (2K)
Variable length string	CHAR (8K)	CHARACTER (2K)
Variable length string	VARCHAR (8K)	VARCHAR2 (32K)
Variable length string	NVARCHAR (8K)	NVARCHAR2 (32K)
Variable length string	VARCHAR (8K)	STRING (32K)
Variable length string	no equivalent	LONG (2GB)
Variable length string	no equivalent	CLOB (128 TB)
?	TEXT (deprecated)	?
?	NTEXT (deprecated)	?
?	IMAGE (deprecated)	?
Numeric Data Types
Integer	BIT	NUMBER(1,0)
Integer	TINYINT (1 byte)	SMALLINT, INT, INTEGER, BINARY INTEGER, and PLS_INTEGER
Integer	SMALLINT (2 bytes)	(all up to 38 digits)
Integer	INT (4 bytes)
Integer	BIGINT (8 bytes)
Number	DECIMAL (1 byte)	DEC, DECIMAL,
		NUMERIC, NUMBER
Number	NUMERIC (2 bytes)	(up to 38 digits)
Floating point number	REAL	REAL
		(63 binary digits)
Floating point number	FLOAT	FLOAT
		(126 binary digits)
Floating point numbers	no equivalent	DOUBLE_PRECISION
		(126 binary digits)
Floating point numbers using native machine arithmetic	no equivalent	BINARY_FLOAT (32 bit)
Floating point numbers using native machine arithmetic	no equivalent	BINARY_DOUBLE (64 bit)
Non-negative integers	no equivalent	NATURAL
Not nullable non-negative integers	no equivalent	NATURALN
Only positive integers	no equivalent	POSITIVE
Not nullable non-negative integers	no equivalent	POSITIVEN
-1, 0 or +1 only	no equivalent	SIGNTYPE
Monetary Data Types
	SMALLMONEY	(user definable)
	MONEY	(user definable)
Date, Interval, Time,and Timezone Data Types
Date-Time	DATETIME & TIMSTAMP	DATE
Date-Time (to 1 nanosecond)	no equivalent	TIMESTAMP
Date-Time with timezone	no equivalent	TIMESTAMP WITH TIMEZONE
Date-Time with local timezone	no equivalent	TIMESTAMP WITH LOCAL TIMEZONE
Interval between dates in year and month	no equivalent	INTERVAL YEAR TO MONTH
Interval between dates in day and second	no equivalent	INTERVAL DAY TO SECOND
Boolean
Boolean TRUE / FALSE	no equivalent	BOOLEAN
Binary Data Types
Fixed length binary	BINARY	RAW or LONG RAW
Variable length binary	VARBINARY	RAW OR LONG RAW
	LONGBINARY	LONG RAW
	(not relevant)	MLSLABEL
	BLOB & IMAGE	BLOB
Row Identifiers
	ROWID	ROWID
	(not relevant)	UROWID
Polymorphic Data Types
Any named SQL type or transient type	no equivalent	ANYTYPE
An instance of a given type, with data, plus a description of the type	no equivalent	ANYDATA
Values of the data instances can be of SQL built-in types as well as user-defined types	no equivalent	ANYDATASET
	CURSOR	REFCURSOR
URI Data Types
Store DBURIRefs	no equivalent	DBURIType
Store URLs to external web pages or to files	no equivalent	HTTPURIType
An object type for storing XML	no equivalent	URIType
Expose documents in the XML heirarchy	no equivalent	XDBURIType
Spatial Types
?	no equivalent	SDO_GEOMETRY
?	no equivalent	SDO_GEORASTER
?	no equivalent	SDO_TOPO_GEOMETRY
Media Types
Supports the storage and management of audio data	no equivalent	ORDAudio
Supports storage and management of any type of media data, including audio, image and video data	no equivalent	ORDDoc
Supports the storage and management of image data	no equivalent	ORDImage
Compact representation of the color, texture, and shape information of image data	no equivalent	ORDImageSignature
Supports the storage and management of video data	no equivalent	ORDVideo
Represents a feature that characterizes an image by its average color	no equivalent	SI_AverageColor
Encapsulates color values	no equivalent	SI_Color
Characterizes an image by the relative frequencies of the colors exhibited by samples of the raw image	no equivalent	SI_ColorHistogram
List containing up to four of the image feature	no equivalent	SI_FeatureList
Most significant colors of a rectangle	no equivalent	SI_PositionalColor
Inherent image characteristics such as height, width, and format	no equivalent	SI_Stillimage
Size of repeating items coarseness, contrast,  and predominant direction	no equivalent	SI_Texture

 

by.psoug