Data Types

Manifold SQL data types consist of several primary data types and several valid synonyms recognized for these data types.

 

The following table lists the primary data types. The synonyms are identified in the SQL Reserved Words / Index topic.

 

Data type

Storage size

Description

BINARY

1 byte per character

Any type of data may be stored in a column of this type. No translation of the data (for example, to text) is made. How the data is input in a binary column dictates how it will appear as output.

BIT

1 byte

Yes and No values and columns that contain only one of two values.

BYTE

1 byte

An integer value between 0 and 255.

COORDSYS

variable

A coordinate system token.

CURRENCY

8 bytes

A scaled integer between -9.2E14 and 9.2E14.

DATETIME

16 bytes

A date or time value between the years 100 and 9999.

DOUBLE

8 bytes

A double-precision floating-point value with a range of  -1.8E308 to 1.8E308

GEOM

variable

A geometric object, includes coordinate system data.

GEOMSDE

variable

A geometric object in ESRI ArcSDE format.

GEOMSHP

variable

A geometric object in ESRI SHP format (geodatabase).

GEOMWKB

variable

A geometric object in OGC WKB format.

LATITUDE

8 bytes

A double-precision floating-point latitude value.

LONG

4 bytes

A long integer between  - 2,147,483,648 and 2,147,483,647.

LONGITUDE

8 bytes

A double-precision floating-point longitude value.

SHORT

2 bytes

A short integer between  - 32,768 and 32,767.

SINGLE

4 bytes

A single-precision floating-point value with a range of  -3.4E38 to 3.4E38.

TEXT

2 bytes per character (Unicode)

Zero to a maximum of 2 gigabytes.

 

Synonyms

 

The following table lists synonym names for the primary data types used in Manifold SQL:

 

Synonym

Data Type

CHAR

TEXT

CHARACTER

TEXT

DATE

DATETIME

INT

LONG

INTEGER

LONG

LONGBINARY

BINARY

LONGTEXT

TEXT

MONEY

CURRENCY

REAL

SINGLE

SMALLINT

SHORT

TIME

DATETIME

TIMESTAMP

DATETIME

TINYINT

BYTE

VARCHAR

TEXT

 

String Literals

 

SQL is case insensitive for SQL itself but is case sensitive when evaluating string values. Suppose, for example, we have a query such as:

 

SELECT [F] FROM [Table] WHERE [F] = "13AF";

 

This query will return no results if the value in "F" is "13aF", because that is not the same as "13AF".

 

To normalize case, use the Transform toolbar Make Lower Case or Make Upper Case operators to force all values in the column to upper case or lower case. Alternatively, use a modified SELECT statement:

 

SELECT [F] FROM [Table] WHERE UCase([F]) = "13AF";

 

When comparing strings, SQL is sensitive to leading or trailing whitespace. "13AF " does not equal "13AF". To remove leading / trailing whitespace use the Transform toolbar Trim Left, Trim Right or Trim operators to eliminate leading, trailing or both leading and trailing whitespace. Alternatively, use a modified SELECT statement:

 

SELECT [F] FROM [Table] WHERE Trim([F]) = "13AF";

 

The strings are enclosed either in apostrophes (') or in quotes (") depending on whether the query is ANSI-compatible or not.

 

Date Literals

 

The date literals used in SQL must use either ANSI format (year-month-day hour:minute:second) or one of the formats mentioned in the Regional Options applet in the Windows Control Panel.

 

To find records dated July 10, 1992 on a machine with US regional options, use either of the following statements:

 

SELECT * FROM [Orders] WHERE [Shipped Date] = #92-7-10#;

SELECT * FROM [Orders] WHERE [Shipped Date] = #7/10/92#;

 

The dates are enclosed in hash signs (#). They can also be enclosed in apostrophes (') or in quotes (") depending on whether the query is ANSI-compatible or not.

 

See Also

 

Expressions

Spatial Extensions

Geocoding Extensions

Raster Extensions