oracle sybase 비교

2017. 3. 27. 18:19database/oracle

 

아직도 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