SQL: 07 - DataType QuickRef

Data type

Description

CHARACTER(n)

Character string. Fixed-length n

VARCHAR(n) or

CHARACTER VARYING(n)

Character string. Variable length. Maximum length n

BINARY(n)

Binary string. Fixed-length n

BOOLEAN

Stores TRUE or FALSE values

VARBINARY(n) or

BINARY VARYING(n)

Binary string. Variable length. Maximum length n

INTEGER(p)

Integer numerical (no decimal). Precision p

SMALLINT

Integer numerical (no decimal). Precision 5

INTEGER

Integer numerical (no decimal). Precision 10

BIGINT

Integer numerical (no decimal). Precision 19

DECIMAL(p,s)

Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal

NUMERIC(p,s)

Exact numerical, precision p, scale s. (Same as DECIMAL)

FLOAT(p)

Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision

REAL

Approximate numerical, mantissa precision 7

FLOAT

Approximate numerical, mantissa precision 16

DOUBLE PRECISION

Approximate numerical, mantissa precision 16

DATE

Stores year, month, and day values

TIME

Stores hour, minute, and second values

TIMESTAMP

Stores year, month, day, hour, minute, and second values

INTERVAL

Composed of a number of integer fields, representing a period of time, depending on the type of interval

ARRAY

A set-length and ordered collection of elements

MULTISET

A variable-length and unordered collection of elements

XML

Stores XML data

Data type

Access

SQLServer

Oracle

MySQL

PostgreSQL

boolean

Yes/No

Bit

Byte

N/A

Boolean

integer

Number (integer)

Int

Number

Int

Integer

Int

Integer

float

Number (single)

Float

Real

Number

Float

Numeric

currency

Currency

Money

N/A

N/A

Money

string (fixed)

N/A

Char

Char

Char

Char

string (variable)

Text (<256)

Memo (65k+)

Varchar

Varchar

Varchar2

Varchar

Varchar

binary object

OLE Object Memo

Binary (fixed up to 8K)

Varbinary (<8K)

Image (<2GB)

Long

Raw

Blob

Text

Binary

Varbinary

Operator

Description

=

Equal

<>

Not equal. Note: In some versions of SQL this operator may be written as !=

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

LIKE

Search for a pattern

IN

To specify multiple possible values for a column