SyBase TRUNCNUM(截取数值)

TRUNCNUM Function [Numeric]

Truncates a number at a specified number of places after the decimal point.

Syntax

TRUNCNUM ( numeric-expression, integer-expression )

Parameters

Parameter

Description

numeric-expression

The number to be truncated.

integer-expression

A positive integer specifies the number of significant digits to the right of the decimal point at which to round. A negative expression specifies the number of significant digits to the left of the decimal point at which to round.

Returns

NUMERIC

Examples

The following statement returns the value 600:

SELECT TRUNCNUM( 655, -2 ) FROM iq_dummy

The following statement: returns the value 655.340:

SELECT TRUNCNUM( 655.348, 2 ) FROM iq_dummy

Usage

This function is the same as TRUNCATE, but does not cause keyword conflicts.

You can use combinations of ROUND, FLOOR, and CEILING to provide similar functionality.

Standards and Compatibility

• SQL—Vendor extension to ISO/ANSI SQL grammar.

• Sybase—Not supported by Adaptive Server Enterprise.

Parent topic: Alphabetical List of Functions

Related reference

ROUND Function [Numeric]

TRUNCUM ，它功能是截取数值，不四舍五入。
      如上面红色的两行所知，当你取两位小数之后，你会发现后面还有一个零（一般查出来之后的看不见是看不到零的，你可以截取数之后，再转成字符串会更看的更明白），
我们所需要的是“655.34””而不是“655.340”，这是Sybase IQ数据库的BUG，我的解决办法是 CAST(655.348 AS DECIMAL(32, 0)) ， 最后得出的结果是“655.34”。

Numeric data types

Description

For storing numerical data.

Syntax

[ UNSIGNED ] BIGINT
[ UNSIGNED ] { INT | INTEGER }
SMALLINT
TINYINT
DECIMAL [ ( precision [ , scale ] ) ]
NUMERIC [ ( precision [ , scale ] ) ]
DOUBLE
FLOAT [ ( precision ) ]
REAL
Usage

BIGINT A signed 64-bit integer, requiring 8 bytes of storage.

      You can specify integers as UNSIGNED. By default the data type is signed. Its range is between -9223372036854775808 and 9223372036854775807 (signed) or from 0 to 18446744073709551615 (unsigned).

      INT or INTEGER A signed 32-bit integer with a range of values between -2147483648 and 2147483647 requiring 4 bytes of storage.

      The INTEGER data type is an exact numeric data type; its accuracy is preserved after arithmetic operations.

     You can specify integers as UNSIGNED; by default the data type is signed. The range of values for an unsigned integer is between 0 and 4294967295.

     SMALLINT A signed 16-bit integer with a range between -32768 and 32767, requiring 2 bytes of storage.

     The SMALLINT data type is an exact numeric data type; its accuracy is preserved after arithmetic operations.

     TINYINT An unsigned 8-bit integer with a range between 0 and 255, requiring 1 byte of storage.

     The TINYINT data type is an exact numeric data type; its accuracy is preserved after arithmetic operations.

     DECIMAL A signed decimal number with precision total digits and with scale of the digits after the decimal point. The precision can equal 1 to 126, and the scale can equal 0 up to precision value. The defaults are scale = 38 and precision = 126. Results are calculated based on the actual data type of the column to ensure accuracy, but you can set the maximum scale of the result returned to the application. For more information, see the “MAX_CLIENT_NUMERIC_SCALE option” on page 420 and the SET OPTION statement in Reference: Statements and Options.

   Table 3-2 lists the storage required for a decimal number.Table 3-2: Storage size for a decimal number

Precision

Storage

1 to 4

2 bytes

5 to 9

4 bytes

10 to 18

8 bytes

19 to 126

See below

The storage requirement in bytes for a decimal value with a precision greater than 18 can be calculated using the following formula:

4 + 2 * (int(((prec - scale) + 3) / 4) +int((scale + 3) / 4) + 1)

where int takes the integer portion of its argument. The storage used by a column is based upon the precision and scale of the column. Each cell in the column has enough space to hold the largest value of that precision and scale. For example:

NUMERIC(18,4) takes 8 bytes per cellNUMERIC(19,4) takes 16 bytes per cell

The DECIMAL data type is an exact numeric data type; its accuracy is preserved to the least significant digit after arithmetic operations. Its maximum absolute value is the number of nines defined by [precision - scale], followed by the decimal point, and then followed by the number of nines defined by scale. The minimum absolute nonzero value is the decimal point, followed by the number of zeros defined by [scale - 1], then followed by a single one. For example:

NUMERIC (3,2) Max positive = 9.99 Min non-zero = 0.01 Max negative = -9.99

If neither precision nor scale is specified for the explicit conversion of NULL to NUMERIC, the default is NUMERIC(1,0). For example,

SELECT CAST( NULL AS NUMERIC ) A,       CAST( NULL AS NUMERIC(15,2) ) B

is described as:

A NUMERIC(1,0)B NUMERIC(15,2)

NUMERIC Same as DECIMAL.

DOUBLE A signed double-precision floating-point number stored in 8 bytes. The range of absolute, nonzero values is between 2.2250738585072014e-308 and 1.797693134862315708e+308. Values held as DOUBLE are accurate to 15 significant digits, but might be subject to rounding errors beyond the fifteenth digit.

The DOUBLE data type is an approximate numeric data type; it is subject to rounding errors after arithmetic operations.

FLOAT If precision is not supplied, the FLOAT data type is the same as the REAL data type. If precision supplied, then the FLOAT data type is the same as the REAL or DOUBLE data type, depending on the value of the precision. The cutoff between REAL and DOUBLE is platform-dependent, and it is the number of bits used in the mantissa of single-precision floating point number on the platform.

When a column is created using the FLOAT data type, columns on all platforms are guaranteed to hold the values to at least the specified minimum precision. In contrast, REAL and DOUBLE do not guarantee a platform-independent minimum precision.

The FLOAT data type is an approximate numeric data type; it is subject to rounding errors after arithmetic operations.

REAL A signed single-precision floating-point number stored in 4 bytes. The range of absolute, nonzero values is 1.175494351e-38 to 3.402823466e+38. Values held as REAL are accurate to 6 significant digits, but might be subject to rounding errors beyond the sixth digit.

The REAL data type is an approximate numeric data type; it is subject to rounding errors after arithmetic operations.


Notes

The INTEGER, NUMERIC, and DECIMAL data types are sometimes called exact numeric data types, in contrast to the approximate numeric data types FLOAT, DOUBLE, and REAL. Only exact numeric data is guaranteed to be accurate to the least significant digit specified after arithmetic operations.

Do not fetch TINYINT columns into Embedded SQL variables defined as CHAR or UNSIGNED CHAR, since the result is an attempt to convert the value of the column to a string and then assign the first byte to the variable in the program.

Indexes

The CMP and HNG index types do not support the FLOAT, DOUBLE, and REAL data types, and the HG index type is not recommended.

The WD, DATE, TIME, and DTTM index types do not support the numeric data types.

Compatibility

• In embedded SQL, fetch TINYINT columns into 2-byte or 4-byte integer columns. Also, to send a TINYINT value to a database, the C variable should be an integer.

• Adaptive Server Enterprise 12.5.x versions do not support unsigned integers. You can map Sybase IQ unsigned integers to Adaptive Server Enterprise signed integers or numeric data, and the data are converted implicitly.

  • Map IQ UNSIGNED SMALLINT data to ASE INT

  • If you have negative values, map IQ UNSIGNED BIGINT to ASE NUMERIC (precision, scale)

    To avoid performance issues for cross-database joins on UNSIGNED BIGINT columns, the best approach is to cast to a (signed) BIGINT on the Sybase IQ side.

• You should avoid default precision and scale settings for NUMERIC and DECIMAL data types, as these differ by product:

  Database

  Default precision

  Default scale

  Sybase IQ

  126

  38

  Adaptive Server Enterprise

  18

  0

  SQL Anywhere

  30

  6

• The FLOAT ( p ) data type is a synonym for REAL or DOUBLE, depending on the value of p. For Adaptive Server Enterprise, REAL is used for p less than or equal to 15, and DOUBLE for p greater than 15. For Sybase IQ, the cutoff is platform-dependent, but on all platforms, the cutoff value is greater than 22.

• Sybase IQ includes two user-defined data types, MONEY and SMALLMONEY, which are implemented as NUMERIC(19,4) and NUMERIC(10,4) respectively. They are provided primarily for compatibility with Adaptive Server Enterprise.