Actian X 11.2 | Scalar Functions

SQL Reference Guide > SQL Reference Guide > Understanding the Elements of SQL Statements > SQL Functions > Scalar Functions

Was this helpful?

Scalar Functions

The scalar functions require either one or two single-value arguments. Scalar functions can be nested to any level.

Note: If II_DECIMAL is set to comma, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:

SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;

Conversion Functions

Conversion functions convert the expression from one data type into another type. Type conversions can also be specified using the CAST expression (see CAST Expression).

ANSIDATE

ANSIDATE(expr)

Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, ANSIDATE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

Result type: ANSIDATE

Converts the expression to internal ANSIDATE representation.

BOOLEAN

BOOLEAN(expr)

Operand type: CHAR, VARCHAR, C, TEXT

Result type: BOOLEAN

Converts the string 'FALSE' and 'TRUE', without regard to case and where applicable, trailing or internal whitespace, to the corresponding BOOLEAN values.

Operand type: INTEGER1, SMALLINT, INTEGER4, BIGINT

Result type: BOOLEAN

Converts the value 0 to FALSE and the value 1 to TRUE.

BYTE or BINARY

BYTE(expr [, len])

BINARY(expr [, len])

Operand type: Any

Result type: BYTE

Converts the expression to BYTE binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, it is padded using null bytes.

Note: Leftmost refers to the leftmost byte in memory. This can return a different result for the same variable value depending on whether the machine is big or little endian.

Note: The binary data depends on the data type selected during the coercion of the literal in expr. This may not be the data type the user expects. For example, a very large integer literal that is too large to be stored as an INTEGER8 will be coerced into a FLOAT.

C(expr [, len])

Operand type: Any except long data types

Result type: C

Converts argument to C string. The optional length argument specifies the length of the result in the range 1 to 32000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, it is padded using space characters.

CHAR

CHAR(expr [, len])

Operand type: Any

Result type: CHAR

Converts argument to char string. The optional length argument specifies the length of the result in the range 1 to 32000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, it is padded using space characters.

Caution! Results may differ from your expectations. For example, SELECT CHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and CHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with CHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT CHAR(0x41). Or use the CHR() function instead of CHAR.

DATE or INGRESDATE

DATE(expr)

INGRESDATE(expr)

Operand type: C, TEXT, CHAR, VARCHAR, NCHAR, NVARCHAR

Result type: INGRESDATE

Converts a C, CHAR, VARCHAR, TEXT, NCHAR, or NVARCHAR string to internal date representation.

Note: When DATE_ALIAS is set to ANSIDATE, DATE('NOW') behaves like CURRENT_TIMESTAMP and not like CURRENT_DATE.

DECIMAL or NUMERIC

DECIMAL(expr [,precision[,scale]])

NUMERIC(expr [,precision[,scale]])

Operand type: Any except ingresdate and ANSI datetime

Result type: DECIMAL

Converts any numeric expression to a decimal value. If scale (number of decimal digits) is omitted, the scale of the result is 0. If precision (total number of digits) is omitted, the precision of the result is determined by the data type of the operand, as follows:

Operand Data Type	Default Precision
tinyint	5
smallint	5
integer	11
bigint	19
float	15
float4	15
decimal	15
money	15

Decimal overflow occurs if the result contains more digits to the left of the decimal point than the specified or default precision and scale can accommodate.

DOW

DOW(expr)

Operand type: Any absolute date

Result type: C

Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.

FLOAT4

FLOAT4(expr)

Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL, INTEGER1, SMALLINT, INTEGER4

Result type: FLOAT4

Converts the specified expression to FLOAT4. Numeric overflow can occur if the argument is too large for the result type (possible from string or decimal).

The range of values for float4 is processor dependent.

FLOAT8

FLOAT8(expr)

Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER4

Result type: FLOAT

Converts the specified expression to FLOAT. Numeric overflow can occur if the argument is too large for a float (possible from string or decimal).

The range of values for float is determined by IEEE_754.

HEX

HEX(expr)

Operand type: Any

Result type: VARCHAR

Returns the hexadecimal representation of the internal Actian X form of the argument expression. The length of the result is twice the length of the argument, because the hexadecimal equivalent of each byte of the argument requires two bytes. For example, hex('ABC') returns '414243' (ASCII) or 'C1C2C3' (EBCDIC). Also, hex(int4(125)) returns '0000007D', the hexadecimal equivalent of the 4 byte binary integer 125.

INT1 or TINYINT

INT1(expr)

TINYINT(expr)

Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER4, BOOLEAN

Result type: INTEGER1

Converts the expression to INTEGER1. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

INT2 or SMALLINT

INT2(expr)

SMALLINT(expr)

Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER4, BOOLEAN

Result type: SMALLINT

Converts the expression to SMALLINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

INT4 or INT or INTEGER

INT4(expr)

INT(expr)

INTEGER(expr)

Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER4, BOOLEAN

Result type: INTEGER4

Converts the expression to INTEGER4. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

INT8 or BIGINT

INT8(expr)

BIGINT(expr)

Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER4, BOOLEAN

Result type: BIGINT

Converts the expression to BIGINT. Decimal, floating point, and string values are truncated by discarding the fractional portion of the argument. Numeric overflow occurs if the argument is too large for the result type.

INTERVAL_DTOS

INTERVAL_DTOS(expr)

Operand type: C, CHAR, VARCHAR, TEXT, INGRESDATE, INTERVAL DAY TO SECOND

Result type: INTERVAL DAY TO SECOND

Converts the expression to internal INTERVAL DAY TO SECOND representation.

INTERVAL_YTOM

INTERVAL_YTOM(expr)

Operand type: C, CHAR, VARCHAR, TEXT, INGRESDATE, INTERVAL YEAR TO MONTH

Result type: INTERVAL YEAR TO MONTH

Converts the expression to internal INTERVAL YEAR TO MONTH representation.

LONG_BYTE or LONG_BINARY

LONG_BYTE(expr)

LONG_BINARY(expr)

Operand type: Any

Result type: LONG BYTE

Converts the expression to LONG BYTE binary data.

LONG_VARCHAR

LONG_VARCHAR(expr [, len])

Operand type: C, CHAR, VARCHAR, TEXT, LONG VARCHAR, LONG NVARCHAR, LONG BYTE

Result type: LONG VARCHAR

Converts the expression to LONG VARCHAR. The optional length argument specifies the length of the result in the range 0 to 32000 bytes. Values outside the range are adjusted to the minimum or maximum, accordingly.

LONG_NVARCHAR

LONG_NVARCHAR(expr)

Operand type: C, CHAR, VARCHAR, TEXT, LONG VARCHAR, LONG NVARCHAR, LONG BYTE

Result type: LONG NVARCHAR

Converts the expression to LONG NVARCHAR.

MONEY

MONEY(expr)

Operand type: C, CHAR, VARCHAR, TEXT, FLOAT, MONEY, DECIMAL INTEGER1, SMALLINT, INTEGER4

Result type: MONEY

Converts the expression to internal MONEY representation. Rounds floating point and decimal values, if necessary.

NCHAR

NCHAR(expr [, len])

Operand type: Any

Result type: NCHAR

Converts argument to NCHAR Unicode string. The optional length argument specifies the length of the result in the range 1 to 16000 bytes. Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, it is padded using space characters.

Caution! Results may differ from your expectations. For example, SELECT NCHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and NCHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with NCHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT NCHAR(0x41). Or use the CHR() function instead.

NVARCHAR

NVARCHAR(expr [, len])

Operand type: Any

Result type: NVARCHAR

Converts argument to NVARCHAR Unicode string. The optional length argument specifies the length of the result in the range 1 to 16000 bytes. Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, the varying length is set to match the character length of the expr.

Caution! Results may differ from your expectations. For example, SELECT NVARCHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and NVARCHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with NVARCHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT NVARCHAR(0x41). Or use the CHR() function instead.

OBJECT_KEY

OBJECT_KEY(expr)

Operand type: VARCHAR, CHAR, C, TEXT

Result type: OBJECT_KEY

Converts the operand to an OBJECT_KEY.

TABLE_KEY

TABLE_KEY(expr)

Operand type: VARCHAR, CHAR, C, TEXT

Result type: TABLE_KEY

Converts the operand to a TABLE_KEY.

TEXT

TEXT(expr [, len])

Operand type: Any

Result type: TEXT

Converts argument to TEXT string. The optional length argument specifies the length of the result in the range 1 to 32,000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.

TIME_LOCAL

TIME_LOCAL(expr)

Operand type: C, TEXT, CHAR, VARCHAR, INGRESDATE, TIME WITHOUT TIME ZONE, TIME WITH TIME ZONE, TIME WITH LOCAL TIME ZONE, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

Result type: TIME WITH LOCAL TIME ZONE

Converts the expression to internal TIME WITH LOCAL TIME ZONE representation.

TIME_WITH_TZ

TIME_WITH_TZ(expr)

Result type: TIME WITH TIME ZONE

Converts the expression to internal TIME WITH TIME ZONE representation.

TIME or TIME_WO_TZ

TIME(expr)

TIME_WO_TZ(expr)

Result type: TIME WITHOUT TIME ZONE

Converts the expression to internal TIME WITHOUT TIME ZONE representation.

TIMESTAMP_LOCAL

TIMESTAMP_LOCAL(expr)

Result type: TIMESTAMP WITH LOCAL TIME ZONE

Converts the expression to internal TIMESTAMP WITH LOCAL TIME ZONE representation.

TIMESTAMP_WITH_TZ

TIMESTAMP_WITH_TZ(expr)

Result type: TIMESTAMP WITH TIME ZONE

Converts the expression to internal TIMESTAMP WITH TIME ZONE representation.

TIMESTAMP or TIMESTAMP_WO_TZ

TIMESTAMP(expr)

TIMESTAMP_WO_TZ(expr)

Result type: TIMESTAMP WITHOUT TIME ZONE

Converts the expression to internal TIMESTAMP WITHOUT TIME ZONE representation.

TO_CHAR

TO_CHAR (datetime [,format])

Operand type: ANSIDATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE

Result type: VARCHAR

Converts a datetime or interval value to a value of VARCHAR data type in the specified date format. If you omit format, then date is converted to a VARCHAR value as follows:

• ANSIDATE values are converted to values in the default date format.

• TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.

• TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.

The format can be any of the following：

Format	Description
punctuation "text"	Preserves literal punctuation and quoted text
AD A.D.	AD indicator
AM A.M. PM P.M.	AM/PM indicator
D	Number of day of week 1 - 7
DAY	Day name padded to suitable width to retain column
DD	Day of month 1 - 31
DDD	Day of year 1 - 366
DY	Day name in 3 characters
FF [1..9]	Fractional seconds
FM	Toggle whether formats have leading or trailing blanks
FX	Toggle whether exact matching is required
HH	Hour of day 1 - 12
HH12	Hour of day 1 - 12 (Supported in TO_CHAR only.)
HH24	Hour of day 0 - 23
J	Julian day (days since January 1, 4712 BC)
MI	Minutes 0 - 59
MM	Month 01 - 12
MON	First 3 characters of month name
MONTH	Month name padded to suitable length
RM	Month in Roman numerals
RR	Round year in 2 digits
RRRR	Round year in 2 or 4 digits
SS	Seconds 0 - 59
SSSS	Seconds since midnight 0 - 86399
TZH	Time zone hour
TZM	Time zone minute
X	Decimal
Y,YYY	Year with comma in this position
YYYY YYY YY Y	Last 4, 3, 2, or 1 digits of year

For TO_CHAR, the case and punctuation of the format are important. Formats whose results are alphabetic follow the case of the format. For example, 'DY' will yield 'MON' but 'Dy' will yield 'Mon'.

SELECT TO_CHAR('2013-07-30 23:42:00.290533-07:00','YEAR-MONTH-DAY HH24:MI:SSXFF6 tzh:tzm');

returns 2013-JULY-TUESDAY 23:42:00.290533 -07:00

SELECT TO_CHAR(SYSDATE,'YYYY-MON-DD');

returns 2013-JUL-30

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');

returns 2013-07-30 23:36:19

TO_DATE

TO_DATE(char [,format])

Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR

Result type: ANSIDATE

Converts char to a value of ANSIDATE data type.

The format specifies the datetime format of char. When you omit format, then char must be in the default date format. Valid values for format are shown under TO_CHAR. The case and punctuation of the format are not important.

If any part of the date is omitted the first month or first month and first date is automatically added.

The following examples converts a character string into a date:

SELECT TO_DATE('2012-Dec-17', 'YYYY-MON-DD'); returns 2012-12-17.

SELECT TO_DATE('20121217', 'YYYYMMDD'); returns 2012-12-17.

SELECT TO_DATE('2012-Dec', 'YYYY-MON'); returns 2012-12-01.

SELECT TO_DATE('2012', 'YYYY'); returns 2012-01-01.

TO_TIME

TO_TIME_(char [,format])

Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR

Result type: TIME

Converts char to a value of TIME data type.

The format specifies the datetime format of char. When you omit format, then char must be in the default timestamp format. Valid values for format are shown under TO_CHAR. The case and punctuation of the format are not important.

If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.

The following example converts a character string into a timestamp:

SELECT TO_TIME('10:01:59','HH24:MM:SS');

returns 10:01:59

TO_TIMESTAMP

TO_TIMESTAMP(char [,format])

Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR

Result type: TIMESTAMP

Converts char to a value of TIMESTAMP data type.

If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.

The following example converts a character string into a timestamp:

SELECT TO_TIMESTAMP('2012-Dec-17', 'YYYY-MON-DD');

returns 2012-12-17 00:00:00.000000

SELECT TO_TIMESTAMP('20121217121314', 'YYYYMMDDHH24MISS');

returns 2012-12-17 12:13:14.000000

SELECT TO_TIMESTAMP ('2012-Dec', 'YYYY-MON');

returns 2012-12-01 00:00:00.000000

SELECT TO_TIMESTAMP ('2012', 'YYYY');

returns 2012-01-01 00:00:00.000000

SELECT TO_TIMESTAMP('2013-10-11 20:01:59.123456','YYYY-MM-DD HH24:MI:SSXFF6');

returns 2013-10-11 20:01:59.123456

TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ(char [,format])

Operand type: CHAR, VARCHAR, NCHAR, or NVARCHAR

Result type: TIMESTAMP WITH TIME ZONE

Converts char to a value of TIMESTAMP data type.

If any part of the date is omitted the first month or first month and first date is automatically added. The time component is optional. If omitted the default is 00:00:00.

The following example converts a character string into a timestamp:

SELECT TO_TIMESTAMP_TZ('2012-Dec-17', 'YYYY-MON-DD TZH:MZM');

returns 2012-12-17 00:00:00.000000-08:00

SELECT TO_TIMESTAMP_TZ('2013-10-11 20:01:59.123456-04:00','YYYY-MM-DD HH24:MI:SSXFF6 tzh:tzm');

returns 2013-10-11 20:01:59.123456-04:00

UNHEX

UNHEX(expr)

Operand type: Any

Result type: VARCHAR

Returns the opposite of the hex function. For example, unhex('61626320') returns 'abc ' and unhex('01204161') returns '\001Aa'.

Exceptions can occur when a “c” data type suppresses the display of certain stored characters, or when the output data type differs from the input type.

Note: Normally one character is generated for every two hex digits being converted to a printable character. If the hex digit pair being converted does not translate to a printable character, the value is converted to a backslash (\), followed by the numeric value of the hex digit pair as a three-digit octal value.

VARBYTE or VARBINARY

VARBYTE(expr [, len])

VARBINARY(expr [, len])

Operand type: Any

Result type: BYTE VARYING

Converts the expression to BYTE VARYING binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.

VARCHAR

VARCHAR(expr [, len])

Operand type: Any

Result type: VARCHAR

Converts argument to VARCHAR string. The optional length argument specifies the length of the result in the range 1 to 32,000 bytes (or 1 to 16000 bytes for a UTF8 installation). Values outside the range are adjusted to the minimum or maximum, accordingly. If len exceeds the length of the expr string, the varying length is set to match the length of the expr.

Caution! Results may differ from your expectations. For example, SELECT VARCHAR(65) returns 65 because the integer 65 is first implicitly coerced to the string '65', and VARCHAR('65') is '65'. Some other database systems may instead interpret this as the ASCII character code, with VARCHAR(65) returning 'A'. To get the latter result, specify hex notation--for example, SELECT VARCHAR(0x41). Or use the CHR() function instead.

Length of Results for Data Type Conversions

When converting decimal values to strings, the length of the result depends on the precision and scale of the decimal column.

Some functions have an optional length parameter. If this parameter is omitted, the length of the result returned by the data type conversion functions c(), char(), varchar(), nchar(), nvarchar(), and text() are as follows:

Data Type of Argument	Result Length
byte	Length of operand
byte varying	Length of operand
c	Length of operand
char	Length of operand
decimal	Depends on precision and scale of column
float & float4	11 characters; 12 characters on IEEE computers
ingresdate	25 characters
integer1 (tinyint)	Maximum 4 characters
integer2 (smallint)	Maximum 6 characters
integer	Maximum 11 characters
integer8 (bigint)	Maximum 19 characters
long byte	Length of operand
long varchar	Length of operand
long nvarchar	2 x length of operand
nchar	2 x length of operand
nvarchar	2 x length of operand
money	20 characters
text	Length of operand
varchar	Length of operand

Numeric Functions

For trigonometric functions (COS, SIN, TAN), specify argument in radians. To convert degrees to radians, use the formula: radians = degrees / 360 * 2 * pi(). The functions ACOS, ASIN, ATAN, AND ATAN2 return a value in radians.

ABS

ABS(n)

Operand type: All numeric types and MONEY

Result type: Same as n

Absolute value of n

ACOS

ACOS(n)

Operand type: All numeric types

Result type: FLOAT

Arccosine of cosine value n

ASIN

ASIN(n)

Operand type: All numeric types

Result type: FLOAT

Arcsine value of sine value n

ATAN

ATAN(n)

Operand type: All numeric types

Result type: FLOAT

Arctangent of n; returns a value from (-pi/2) to pi/2.

ATAN2

ATAN2 (x, y)

Operand type: All numeric types

Result type: FLOAT

Arctangent of angle defined by coordinate pair (x, y)

CEIL

CEIL(n)

CEILING(n)

Operand type: All numeric types and MONEY

Result type: Numeric type, based on operand.

Returns the smallest whole value greater than or equal to the specified numeric expression.

Returns a decimal if input is decimal, and a float if input is float. Coerces other types to decimal or float, according to normal coercion preferences for the input type, and then returns result based on the coerced input.

COS

COS(n)

Operand type: All numeric types

Result type: FLOAT

Cosine of n; returns a value from -1 to 1.

EXP

EXP(n)

Operand type: All numeric types and MONEY

Result type: FLOAT

Exponential of n

FLOOR

FLOOR(n)

Operand type: All numeric types and MONEY

Result type: Numeric type, based on operand.

Returns the largest whole value less than, or equal to, the specified numeric expression.

Returns a decimal if input is decimal, and a float if input is float or money. Coerces other types to decimal or float, according to normal coercion preferences for the input type, and then returns result based on the coerced input.

LOG

LOG(n)

LN(n)

Operand type: All numeric types and MONEY

Result type: FLOAT

Natural logarithm of n

MOD

MOD(n,b)

Operand type: INTEGER1, SMALLINT, INTEGER4, DECIMAL

Result type: Same as b

n modulo b. The result is the same data type as b. Decimal values are truncated.

PI()

Operand type: None

Result type: FLOAT

Value of pi (ratio of the circumference of a circle to its diameter)

POWER

POWER(x,y)

Operand type: All numeric types

Result type: FLOAT

x to the power of y (identical to x ** y)

ROUND

ROUND(n,i)

Operand type: All numeric types

Result type: Type of n.

Rounds value n at the i'th place right or left of the decimal, depending on whether i is greater or less than 0.

WARNING! When used with a floating point argument, rounding errors may occur.

SIGN

SIGN(n)

Operand type: All numeric types and MONEY

Result type: INTEGER4

Returns -1 if n < 0, 0 if n = 0, +1 if n > 0

SIN

SIN(n)

Operand type: All numeric types

Result type: FLOAT

Sine of n; returns a value from -1 to 1.

SQRT

SQRT(n)

Operand type: All numeric types and MONEY

Result type: FLOAT

Square root of n

TAN

TAN(n)

Operand type: All numeric types

Result type: FLOAT

Tangent value of angle n

TRUNC

TRUNC(x ,y)

TRUNCATE(x ,y)

Operand type: All numeric types

Result type: DECIMAL

Truncates x at the decimal point, or at y places to the right or left of the decimal, depending on whether y is greater or less than 0.

String Functions

String functions perform a variety of operations on character data.

String functions can be nested. For example:

LEFT(RIGHT(x.name, SIZE(x.name) - 1), 3)

returns the substring of x.name from character positions 2 through 4.

The || or + operator can also be used to concatenate strings:

x.lastname || ', ' || x.firstname

Note: Using + should be avoided. It is an overloaded operator and can result in ambiguity when string columns and literals that contain only numeric values are mixed with numeric columns or literals.

SELECT '1' + '40' + '50' returns 14050

SELECT 1 + '40' + '50' returns 91

SELECT '1' + 40 + '50' returns 91

SELECT '1' + 40 || '50' returns 4150

ASCII

ASCII(v1)

Result type: Any character type

Returns the character equivalent of the value v1, which is an expression of any type.

BYTEEXTRACT

BYTEEXTRACT(c1,n)

Result type: BYTE

Returns the nth byte of c1. If n is larger than the length of the string, the result is a byte of ASCII 0.

Note: This function does not support LONG VARCHAR, LONG NVARCHAR, or LONG BYTE arguments.

CHARACTER_LENGTH

CHARACTER_LENGTH(c1)

Result type: INTEGER4

Returns the number of characters in c1 without trimming blanks, as is done by the LENGTH() function.

Note: This function does not support NCHAR and NVARCHAR arguments.

CHAREXTRACT

CHAREXTRACT(c1,n)

Result type: VARCHAR or NCHAR

Returns the nth character or code point of c1. If n is larger than the length of the string, the result is a blank character.

Note: For Unicode (USC2) strings the value returned is the single USC2 character. For non-Unicode strings the values returned may be 1,2,3 or 4 bytes long depending on the character at offset n characters of c1.

SELECT CHAREXTRACT('company',4) returns 'p'

CHR

CHR(n)

Result type: CHAR

Converts integer into corresponding ASCII code. If n is greater than 255, the conversion is performed on n mod 256.

SELECT CHR(65) returns 'A'.

SELECT CHR(345) returns 'Y'.

COLLATION_WEIGHT

COLLATION_WEIGHT(c1 [,n1])

Result type: VARBYTE

Returns the collation weight of any char, c, varchar, text, nchar, or nvarchar value c1. n1 is an optional collation ID when the collation weight is desired relative to a specific collation.

CONCAT

CONCAT(c1,c2...)

Result type: Any character or Unicode type, BYTE

Concatenates two or more strings. CONCAT takes a minimum of one operand.

Note: When used in an Ingres 4GL statement, the function concatenates no more than two strings.

SELECT CONCAT('1', '2', '3', '4') returns '1234'.

In earlier releases, to achieve the same result, you must nest the CONCAT function: SELECT CONCAT(CONCAT(CONCAT('1', '2'), '3'), '4').

The result size is the sum of the sizes of the arguments. If the result is a c or char string, it is padded with blanks to achieve the proper length. To determine the data type results of concatenating strings, see the table regarding results of string concatenation.

Note: The concatenation of the BYTE data type cannot be used to create a table column implicitly in Actian X tables; the result of such concatenation, however, can be inserted into a column with a character data type.

Wrong:

CREATE TABLE CONCAT2 AS SELECT CONCAT(BYTE('1'), 0x42, X'43', CAST('4' AS BYTE));

Correct:

CREATE TABLE test_concat (col1 VARCHAR(4);

INSERT INTO test_concat SELECT CONCAT(BYTE('1'), 0x42, X'43', CAST('4' AS BYTE));

DMETAPHONE

DMETAPHONE(c1)

Result type: VARCHAR

Returns the metaphone code from a varchar string (vch1) using the double metaphone algorithm.

Alias: DOUBLE_METAPHONE.

SELECT DMETHAPHONE('XXXXXXX') returns XXXXXXX

GENERATE_DIGIT

GENERATE_DIGIT('scheme', 'c1')

Result type: VARCHAR(2)

Generates the check digit for the string c1 using the specified encoding scheme.

Supported schemes are described under VALIDATE_DIGIT.

The check digit is computed from the other characters in the string. Although the majority of check digits are a single character, some encoding schemes may return a two character check digit. Typically the check digit is appended to the string for validation, but this is not the case in all encoding schemes.

Note: This function does not append the check digit to the string.

The check digit can be used to help determine if a string has been entered correctly. A check digit may be able to detect several types of errors, including:

• Mistyped characters ('0123' not '0124')

• Omissions or additions of characters

• Adjacent transposition errors ('01234' not '01243')

• Twin errors ('11' becomes '22')

• Jump transpositions (123 becomes 321)

• Jump twin errors (121 becomes 323)

• Phonetic errors—the mixing of similar sounding numbers (sixty '60' not sixteen '16')

A check digit improves the chance of detecting errors in keying, but is not an infallible error detector. Furthermore, a check digit does not provide error correction capability.

There are many check digit schemes, each with its own purpose, capabilities, and complexity. For example, the Luhn algorithm is widely used in credit card numbers and is programmatically simple to implement. The Verhoeff algorithm, in contrast, is better at detecting certain error types such as jump transpositions, but is more complicated.

The scheme name is case insensitive. When specifying the LUHN or VERHOEFF scheme, the function can take as input a character string of any length. The other schemes are fixed length, so the input string must be of the correct length.

Note: This function does not support NCHAR and NVARCHAR strings as arguments.

II_IPADDR

II_IPADDR(n)

Result type: VARBYTE

Converts an IPv4 (dotted-decimal) or IPv6 (coloned-hex) address to a varbyte data type of length 4 or 16 respectively. Each number between the periods of an IPv4 address is converted into a single byte in the hex result. For IPv6, each hex number between the colons is converted to two bytes in the hex result. Double-colons and IPv4-mapped IPv6 addresses are supported as well.

Views and database procedures created prior to Ingres 9.2 will continue to exhibit the old behavior until they are recreated. Previously, only IPv4 addresses were supported and the returned value was type=byte with length=4.

For example:

• 255.255.0.0 produces ‘ffff0000’;

• 10.20.30.40 produces ‘0a141e28’

• 2.1 produces “02000001”

• fe80::208:74ff:fef0:42b3 produces “FE80000000000000020874FFFEF042B3”

• ::1 produces “00000000000000000000000000000001”

• ::FFFF:141.202.36.10 produces “00000000000000000000FFFF8DCA240A”

INITCAP

INITCAP(c1)

Result type: Any character or Unicode type

Converts the first letter of each word in c1 to upper case and the remaining letters in each word to lowercase.

SELECT INITCAP('This is the final version (VERSION:5.a;6) of Leonard''s will')

returns:'This Is The Final Version (Version:5.A;6) Of Leonard's Will'

INSTR

INSTR(c1, c2, n)

Result type: Integer type

Returns the position of the nth occurrence of c2 in c1.

SELECT INSTR('babababa', 'bab', 2) returns 3.

JARO_WINKLER

JARO_WINKLER(c1,c2)

Result type: FLOAT4

Calculates the Jaro-Winkler similarity between two VARCHAR strings. This is returned as a float value between 0 and 1, where 0.0 means no similarities and 1.0 means the strings are identical.

SELECT JARO_WINKLER('same','same') returns 1.0

LEFT

LEFT(c1,len)

Result type: Any byte, character, or Unicode type

Returns the leftmost len characters of c1. If the result is a fixed-length c or char string, it is the same length as c1, padded with blanks. The result format is the same as c1. len will be converted to a positive integer.

If len is not an integer value it will be rounded down equivalent to floor(len). If len is negative the result will be an empty string.

SELECT LEFT ('Company',4) returns 'Comp'

LENGTH

LENGTH(c1)

Result type: SMALLINT (for LONG VARCHAR, returns INTEGER4)

If c1 is a fixed-length c or char string, returns the length of c1 without trailing blanks. If c1 is a variable-length string, returns the number of characters actually in c1.

LEVENSHTEIN

LEVENSHTEIN(c1,c1)

Result type: INTEGER4

Calculates the Levenshtein distance between two VARCHAR strings. The Levenshtein distance between two strings is the minimum number of changes that need to be made to convert the source string into the target string.

Alias: LEVENSHTEIN_DISTANCE

SELECT LEVENSHTEIN('foo','fou') returns 1

LOCATE

LOCATE(c1,c2)

Result type: SMALLINT

Returns the location of the first occurrence of c2 within c1, including trailing blanks from c2. The location is in the range 1 to size(c1). If c2 is not found, the function returns size(c1) + 1. The function size() is described below, in this table.

If c1 and c2 are different string data types, c2 is coerced into the c1 data type.

SELECT LOCATE ('Company', 'p') returns 4

LOWERCASE or LOWER

LOWERCASE(c1)

LOWER(c1)

Result type: Any character or Unicode type

Converts all upper case characters in c1 to lower case.

SELECT LOWER ('Company') returns 'company'

LPAD

LPAD(expr1, n [, expr2])

Result type: Any character type

Returns character expression of length n in which expr1 is prepended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the start of the result string.

SELECT LPAD ('Company',20, '-') returns '-------------Company'

LTRIM

LTRIM(expr)

Result type: Any character type

Returns character expression with leading blanks removed.

SELECT LTRIM (' Company') returns 'Company'

MD5SUM

MD5SUM(c1)

Result type: CHAR(32)

Returns the calculated MD5 message digest (also known as md5sum) of c1. The computed sum generated is a lowercase hexadecimal string.

SELECT MD5SUM('a') returns '0cc175b9c0f1b6a831c399e269772661'.

NOTRIM

NOTRIM(c1)

Result type: Any character string variable

Retains trailing blanks when placing a value in a varchar column. This function can be used only in an embedded SQL program. For more information, see the Embedded SQL Companion Guide.

OCTET_LENGTH or LENGTHB

OCTET_LENGTH(c1[,charset])

LENGTHB(c1[,charset])

Result type: INTEGER4

Returns the number of 8-bit octets (bytes) in c1 without trimming blanks, as is done by the LENGTH() function.

The result when multi-character datasets are used will be longer as each byte of a multi-bye character is counted.

The second parameter is any valid characterset supported by the database.

CREATE TABLE octet(col1 VARCHAR(10), col2 CHAR(10));

INSERT INTO octet VALUES ('Company', 'Company')

SELECT OCTET_LENGTH (col1) returns 7

SELECT LENGTH (col1) returns 7

SELECT OCTET_LENGTH (col2) returns 10

SELECT LENGTH (col2) returns 7

PAD

PAD(c1)

Result type: TEXT, VARCHAR, or NVARCHAR

Returns c1 with trailing blanks appended. For example: if c1 is a varchar string that can hold fifty characters but only has two characters, pad(c1) appends 48 trailing blanks to c1 to form the result.

Note: This function does not support LONG VARCHAR or LONG NVARCHAR arguments.

POSITION

POSITION(c1 IN c2)

POSITION(c1 , c2)

Result type: SMALLINT

ANSI compliant version of LOCATE function. If a match exists, POSITION(c1 IN c2) is equal to LOCATE(c2, c1). If a match does not exist, POSITION returns 0, unlike LOCATE.

SELECT POSITION('p', 'Company') returns 4.

SELECT POSITION('z', 'Company') returns 0.

SELECT LOCATE('Company', 'p') returns 4 (same result as POSITION).

SELECT LOCATE('Company', 'z') returns 8 (one more than LENGTH(‘company’).

REPEAT

REPEAT(c1, n)

Result type: Any character or Unicode type except the long forms

Returns c1 (a character string) repeated n times. REPLICATE is a synonym for REPEAT. The maximum length of the result is 32000 bytes (or 16000 bytes in a UTF8 installation).

Note: This function does not support LONG VARCHAR and LONG NVARCHAR arguments.

REPEAT ('-',10) returns '----------'

REPEAT ('str',3) returns 'strstrstr'

REPLACE

REPLACE(expr1, expr2, expr3)

Result type: Any byte or character type

Returns character expression derived from expr1 in which all instances of expr2 have been replaced by expr3.

SELECT REPLACE('The priory was in the next town','priory','church')

returns: 'The church was in the next town'

REVERSE

REVERSE(c1)

Result type: CHAR, VARCHAR, NCHAR, NVARCHAR

Returns the string c1 with the in-memory order of the characters reversed, based on code points. For CHAR and NCHAR, the trailing space is also reversed.

Reversal of code points is supported for all string types in both UTF8 and non-UTF8 installations.

In the following examples, each underline character (_) represents a space.

Data Type	Input	Output
CHAR(8)	hello	___olleh
NCHAR(8)	hello	___olleh
VARCHAR(8)	hello	olleh___
NVARCHAR(8)	hello	olleh___

SELECT REVERSE('ecnalubma') returns ambulance.

Example for Unicode input:

SELECT REVERSE('Adán') returns nádA. The string 'Adán' is 4 code points:

• U+0041 Latin Capital Letter A

• U+0064 Latin Small Letter D

• U+00E1 Latin Small Letter A with Acute

• U+006E Latin Small Letter N

REVERSE(c1, 'egc')

Result type: CHAR, VARCHAR, NCHAR, NVARCHAR

Returns the string c1 with the in-memory order of the characters reversed, based on characters consisting of multiple code points (extended grapheme cluster). For CHAR and NCHAR, the trailing space is also reversed.

Reversal of extended grapheme clusters is supported for types CHAR and VARCHAR in case the data is interpreted as UTF8 (due to the installed character set or collation). For types NCHAR and NVARCHAR the reversal of extended grapheme clusters is supported independently of the installed character set.

For more information on extended grapheme clusters, see the Unicode Standard Annex #29 “Unicode Text Segmentation” at https://www.unicode.org/reports/tr29.

Example for Unicode input:

SELECT REVERSE('Adán', 'egc') returns nádA. The string 'Adán' is 5 code points:

• U+0041 Latin Capital Letter A

• U+0064 Latin Small Letter D

• U+0061 Latin Small Letter A

• U+0301 Combining Acute Accent

• U+006E Latin Small Letter N

The a gets acute because U+0061 and U+0301 build a grapheme cluster.

To determine the number of code points in a string, use the LENGTH() function.

RIGHT

RIGHT(c1,len)

Result type: Any byte, character, or Unicode type

Returns the rightmost len characters of c1. Trailing blanks are not removed first. If c1 is a fixed-length character string, the result is padded to the same length as c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1. len must be a positive integer.

SELECT RIGHT('The priory was in the next town',9)

returns 'next town'

RPAD

RPAD(expr1, n [, expr2])

Result type: Any character type

Returns character expression of length n in which expr1 is appended by n-m blanks (where m is length(expr1)) or, if expr2 is coded, enough copies of expr2 to fill n-m positions at the end of the result string.

SELECT RPAD('Company',12, '-') returns 'Company-----'

SELECT RPAD('Company',12, '-x') returns 'Company-x-x-'

RTRIM

RTRIM(expr)

Result type: Any character type

Returns character expression with trailing blanks removed.

SELECT LENGTH(RTRIM('Company ')) returns 7

SHIFT

SHIFT(c1,nshift)

Result type: Any character or Unicode type

Shifts the string nshift places to the right if nshift > 0 and to the left if nshift < 0. If c1 is a fixed-length character string, the result is padded with blanks to the length of c1. If c1 is a variable-length character string, no padding occurs. The result format is the same as c1.

SELECT SHIFT('Company',4) returns ' Com'

SELECT SHIFT('Company',‑4) returns 'any '

SIZE

SIZE(c1)

Result type: smallint

Returns the declared size of c1 without removal of trailing blanks.

SELECT SIZE('Company') returns 7

SOUNDEX

SOUNDEX(c1)

Result type: Any character type

Returns a c1 four-character field that can be used to find similar sounding strings. For example, SMITH and SMYTHE produce the same SOUNDEX code. If there are less than three characters, the result is padded by trailing zero(s). If there are more than three characters, the result is achieved by dropping the rightmost digits.

This standard (Russell) soundex function is useful for finding like-sounding strings quickly. A list of similar sounding strings can be shown in a search list rather than just the next strings in the index.

Note: This function does not support LONG VARCHAR or any Unicode arguments.

SELECT SOUNDEX('Company') returns 'C515'

SELECT SOUNDEX ('Company2012') returns 'C515'

SOUNDEX_DM

SOUNDEX_DM(c1)

Result type: Any character type

Returns one or more six-character codes in a comma-separated varchar string (up to a maximum of 16 codes) that can be used to find similar sounding names.

This Daitch-Mokotoff soundex function returns one or more six-character codes in a comma-separated varchar string (up to a maximum of 16 codes) that can be used to find similar sounding names. For example: the codes 739460,734600 may be returned for the name Peterson.

Leading and embedded spaces are ignored in the input. The input strings are terminated by non-alphabetic characters other than period (.), apostrophe ('), or hyphen (-). The function ignores the first occurrence of any of these characters, but terminates the string on a subsequent occurrence. This allows the input of names or place names with standard punctuation marks. For example:

O'Brien is treated as 'OBRIEN'
St.Kilda is treated as 'STKILDA'
St. Hilda is treated as 'STHILDA'
'Smyth-Brown' is treated as 'SMYTHBROWN'

An empty name, or a name with characters unable to be encoded, returns a code of 000000 and no error is generated.

Note: This function does not support LONG VARCHAR or any Unicode arguments.

For more information, see SOUNDEX_DM vs. SOUNDEX.

SOUNDEX_NYSIIS

SOUNDEX_NYSIIS(c1)

Result type: VARCHAR

Returns the NYSIIS Soundex code from a string. The algorithm, devised in 1970 as part of the New York State Identification and Intelligence System (NYSIIS), is part of the New York State Criminal Justice Services, and is 2.7% more accurate than the traditional Russell Soundex. Although not as accurate as the Daitch-Mokotoff Soundex (see SOUNDEX_DM), it is easier to use the output.

Alias: NYSIIS

SELECT SOUNDEX_NYSIIS('XXXXXXX') returns XXXXXXX

SQUEEZE

SQUEEZE(c1)

Result type: TEXT or VARCHAR

Compresses white space. White space is defined as any sequence of blanks, null characters, newlines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs). Trims white space from the beginning and end of the string, and replaces all other white space with single blanks.

This function is useful for comparisons. The value for c1 must be a string of variable‑length character string data type (not fixed-length character data type). The result is the same length as the argument.

SELECT SQUEEZE (' Company 2012 ') returns 'Company 2012'

SUBSTR

SUBSTR(c1, loc [, len])

Result type: VARBYTE, VARCHAR, NVARCHAR, and LONG variants

Returns part of c1 starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand.

If len is specified and is less than 1, SUBSTR returns NULL.

The loc parameter determines the start of the substring to be extracted. If loc is less than 0 the position is counted backwards from the end of c1; if loc is greater than 0 the position is counted from the beginning. If loc is 0 the start position is the first character.

After the start of the substring is determined, len characters are extracted. If len is not supplied, the rest of c1 is implied.

If c1 is VARCHAR, CHAR, C, or TEXT, the result is a VARCHAR. If c1 is NCHAR or NVARCHAR, the result is an NVARCHAR. If c1 is a BYTE or VARBYTE, the result is a VARBYTE. If c1 is passed as a long data type, the result is of the same format.

SELECT SUBSTR('Company 2012',9,2) returns '20'

SELECT SUBSTR('Company 2012',9) returns '2012'

SELECT SUBSTR('Company 2012',-9,4) returns 'pany'

SUBSTRING

SUBSTRING(s {FROM | ,} loc [ {FOR | ,} len])

Result type: VARBYTE, VARCHAR, NVARCHAR, and LONG variants

Returns part of string s starting at the loc position and either extending to the end of the string or for the number of characters/code points in the len operand. The result format is a varchar or nvarchar the size of s.

SELECT SUBSTRING('Company 2012',9,2) returns '20'

SELECT SUBSTRING('Company 2012',9) returns '2012'

SELECT SUBSTRING('Company 2012',-9,4) returns an empty string.

SUBSTRING(s {LIKE | SIMILAR TO | BEGINNING | ENDING } pattern ESCAPE esc_character [modifier])

Result type: VARCHAR and NVARCHAR

Returns part of string s that matches the segment indicated by the escaped double quote characters in the pattern specified:

LIKE and SIMILAR TO

Returns the substring from s that best matches the pattern.

These use a pattern syntax based on LIKE (see LIKE Predicate) and SIMILAR TO (see SIMILAR TO Predicate) predicates. In addition to the respective pattern syntax, both use a pair of quotation marks (") to divide the pattern into three sub-patterns.

An attempt is made to match string s with the complete pattern and to return the portion of string s that corresponds to the middle sub-pattern. If part of string s can be associated with either the middle or an outer sub-pattern, then the middle wins. If the second quotation mark is missing, it will default to the end of pattern, and thus the third pattern would be empty. If no quotation marks are present NULL is returned regardless of the matching.

If the alternation operator (|) is not used, the arrangement with the longest middle pattern match is returned. If an alternation operator is used, these are sequentially used, left to right so the first alternative that generates a total pattern match is taken, and then longest middle match returned.

BEGINNING

Returns the substring from s that starts with the first occurrence of the pattern up to the end of line.

ENDING

Returns the substring from s that ends with the last occurrence of the pattern. The start of the returned text will be the beginning of s.

ESCAPE esc_character

Specifies an escape character, which suppresses any special meaning for the character following it, allowing the character to be entered literally. For details, see LIKE Predicate.

modifier

Controls the matching using the modifier WITHOUT DIACRITICAL or WITHOUT CASE or their positive equivalents WITH DIACRITICAL and WITH CASE, as described in LIKE Predicate.

Default: If no modifier is specified, WITH DIACRITICAL and WITH CASE are implied.

If no match is found, the NULL value is returned.

The result data type and size will match string s.

SELECT SUBSTRING('Company 2016' LIKE 'Company \"%\"' ESCAPE '\') returns '2016'

The whole pattern must match but think of the patterns as three sub-patterns: a prefix pattern, the contained pattern, and the trailing pattern. The prefix and trailing are matched but the substring result will be the text that corresponds to the pattern between the double quote markers.

SELECT SUBSTRING('Company 2016' SIMILAR TO '% \"[0-9]{4}\"'ESCAPE'\') returns '2016'

The part of the full pattern that is between the double quote markers—that is, ‘[0-9]{4}’—will determine the section of matched text returned.

SELECT SUBSTRING('221b Baker Street, London, SL1 4AA, UK'
SIMILAR TO '%\"[A-Z]{1,2}[0-9]{1,2} [0-9][A-Z]{2}\"%' ESCAPE '\') returns 'SL1 4AA'

SELECT SUBSTRING('Company 2016' BEGINNING 'test') returns NULL

SELECT SUBSTRING('Company 2016' BEGINNING 'p' ESCAPE '\') returns ‘pany 2016’

SELECT SUBSTRING('Company 2016' ENDING 'p' ESCAPE '\') returns ‘Comp’

SELECT SUBSTRING('Company 2016' BEGINNING 'pan\|20'ESCAPE '\') returns 'pany 2016'

SELECT SUBSTRING('Company 2016' BEGINNING '20\|pan'ESCAPE '\') returns '2016'

The alternation operator rules are applied left to right (the same as is used for LIKE and SIMILAR TO).

The CASE and DIACRITICAL qualifiers can be used as well:

SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' ESCAPE '\'
WITH CASE WITH DIACRITICAL) returns 'E'

SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' ESCAPE '\'
WITH CASE WITHOUT DIACRITICAL) returns 'ÈÊË'

SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' ESCAPE '\'
WITHOUT CASE WITH DIACRITICAL) returns 'eE'

SELECT SUBSTRING('êëÈÊËèéêëeEÈ' SIMILAR TO '%\"E+\"%' ESCAPE '\'
WITHOUT CASE WITHOUT DIACRITICAL) returns 'êëÈÊËèéêëeEÈ'

SUBSTRING_INDEX

SUBSTRING_INDEX(str,delim,count)

Result type: VARCHAR

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. This function performs a case-sensitive match when searching for delim.

test(str VARCHAR(20), cnt INT);

INSERT INTO test VALUES('www.actian.com', 2);

INSERT INTO test VALUES('www.actian.com', -2);

SELECT SUBSTRING_INDEX(str, '.', 2) FROM test;

+--------------------+

|col1 |

+--------------------+

|www.actian |

+--------------------+

SELECT SUBSTRING_INDEX(str, '.', cnt) FROM test;

+--------------------+

|col1 |

+--------------------+

|www.actian |

|actian.com |

+--------------------+

Note: This function works only for data stored in tables created with the X100 or X100_ROW storage structure.

TRIM

TRIM(c1)

Result type: TEXT or VARCHAR

Returns c1 without trailing blanks. The result has the same length as c1.

ANSI Compliant Version of TRIM:

TRIM([ [BOTH | LEADING | TRAILING] [c1] FROM] c2 )

Result type: Any character string variable

Returns c2 with all occurrences of c1—which can be only one character—removed from the beginning, end, or both, as specified. BOTH is the default. In the absence of c1, the space is assumed.

SELECT 'INGRES' , '['||TRIM(' Company ' ) ||']'

returns [ Company]

SELECT 'BOTH' , '['||TRIM(BOTH ' ' FROM ' Company ' ) ||']'

returns [Company]

SELECT 'LEADING' , '['||TRIM(LEADING ' ' FROM ' Company ' ) ||']'

returns [Company ]

SELECT 'TRAILING' , '['||TRIM(TRAILING ' ' FROM ' Company ' ) ||']'

returns [ Company]

UPPERCASE or UPPER

UPPERCASE(c1)

UPPER(c1)

Result type: Any character type

Converts all lower case characters in c1 to upper case.

VALIDATE_DIGIT

VALIDATE_DIGIT('scheme', c1)

Result type: INTEGER4

Validates that the check digit in the string c1 is mathematically correct for the specified scheme. The function returns 1 for valid and 0 for invalid.

Restrictions on scheme names and strings are the same as for GENERATE_DIGIT (see GENERATE_DIGIT).

Note:

• The scheme name is case insensitive (can be in mixed case).

• You must not include separators, such as dashes or whitespace, in the string.

• The VALIDATE_DIGIT function does not enforce any internal formatting rules of the scheme. For example, an EAN is split into a GS1 Prefix, the Company Number, an Item reference, and the check digit. This function only calculates the check digit.

The scheme for GENERATE_DIGIT and VALIDATE_DIGIT functions can be one of the following:

EAN

European Article Number

Synonyms: EAN_13, GTIN, GTIN_13, JAN

The EAN is a bar-coding standard defined by the standards organization GS1. It is used worldwide for marking retail goods. It was developed as a superset of the original 12-digit Universal Product Code (UPC) system. It is also called a Japanese Article Number (JAN) in Japan. UPC, EAN, and JAN numbers are collectively called Global Trade Item Numbers (GTIN).

An EAN string is a 13-character string composed entirely of digits. The check digit is the rightmost character in the string.

EAN_8

European Article Number

Synonyms: GTIN_8, RCN_8

The EAN_8 is an 8-character number derived from the longer EAN_13 and is intended for use on small packages where the longer number would be awkward.

EAN_8 can also be used internally by companies to identify restricted or “own-brand” products to be sold within their stores only. These are often referred to with the synonym RCN_8.

An EAN_8 string is an 8-character string composed entirely of digits. The check digit is the rightmost character in the string.

ISBN

International Standard Book Number

The standard ISBN is a 10-character alphanumeric identifier for printed material. The check digit is the rightmost character and can be either a digit or the alpha 'X'. The other characters in the string must be digits.

ISBN_13

International Standard Book Number

ISBN-13 is the 13-character version of the ISBN. The string is composed entirely of digits. The check digit is the rightmost character in the string.

ISSN

International Standard Serial Number

The ISSN is an 8-character alphanumeric identifier for electronic or print periodicals. The check digit is the rightmost character and can be either a digit or the alpha 'X'. The other characters in the string must be digits.

LUHN

The LUHN algorithm

Most credit cards and many government identification numbers are based on this simple algorithm created by Hans Peter Luhn. For example, the Canadian Social Security number is a nine digit number based on the Luhn algorithm.

The string can be of any permitted length, but must be composed entirely of digits. The check digit is the rightmost character in the string.

LUHN_A

The LUHN algorithm (alphanumeric)

LUHN_A is an extension of the Luhn algorithm that allows for alphabetic characters by assigning numbers 10 to 'A', 11 to 'B', ...35 to 'Z'. It is case insensitive.

The Committee on Uniform Security Identification Procedures (CUSIP) provides a 9-character alphanumeric string based on this algorithm. Also the International Securities Identification Number (ISIN) is a 12-character version of this algorithm.

The string can be of any permitted length, but must be composed entirely of alphanumeric characters. The check digit is the rightmost character in the string.

UPC

Universal Product Code

Synonyms: UPC_A, EAN_12

Used to mark retail goods as per the EAN (see above).

The UPC is composed of 12 digits with the check digit being the rightmost character.

UPC_E

Universal Product Code

The UPC_E is composed of six digits and is intended for smaller packages where a full size UPC_A would be cumbersome. Unlike the UPC_A, the check digit is not appended to the number, but is used to determine the “odd/even” parity assigned to the numbers for when they are encoded as barcode lines.

VERHOEFF

The Verhoeff algorithm

Developed by Jacobus Verhoeff, this algorithm detects all transposition errors and other types of errors that are not detected by the Luhn algorithm.

Like the Luhn algorithm, it can handle strings of any permitted length. The string must be composed entirely of digits and the check digit will be the rightmost character.

VERHOEFFNR

Verhoeff algorithm as described in Numerical Recipes in C.

A variation on the Verhoeff algorithm published in Numerical Recipes in C by Press, Teukolsky, Vetterling, and Flannery.

SOUNDEX_DM vs. SOUNDEX

The advantages of the Daitch-Mokotoff soundex over the standard (Russell) soundex function are:

• The extra length of the codes returned avoids false positives on long names with the same base.

For example:

Name	SOUNDEX Code	SOUNDEX_DM Code
Nichols	N242	658400,648400
Nicholson	N242	658460,648460

In this example the soundex() function fails to differentiate between the names, whereas the SOUNDEX_DM() function returns several codes for each name, none of which match any elements in the other.

• The ability to return multiple codes allow for the different sounds certain character combinations make.

For example: Does the “ch” in Cherkassy sound like the “ch” in cheese or in Christmas?

• The ability to return multiple codes allow a greater chance to match names that the standard soundex will miss.

For example:

Name	SOUNDEX Code	SOUNDEX_DM Code
Schwartsenegger	S632	479465
Shwarzenegger	S625	479465,474659
Schwarzenegger	S625	479465,474659

In this example, the soundex() function fails to match the misspelling 'Schwartsenegger', whereas the SOUNDEX_DM() function generates a string with a matching element (479465) for the other two cases listed.

The disadvantage of the Daitch-Mokotoff soundex over the standard soundex function is the computational overhead required to process a multi-element string.

String Functions That Do Not Accept Long Data Types

The following string functions do not accept LONG VARCHAR, LONG NVARCHAR, or LONG BYTE columns:

• BYTEEXTRACT

• CHAREXTRACT

• CONCAT

• INITCAP

• LOCATE

• LPAD

• NOTRIM

• REPEAT

• REPLACE

• RPAD

• SHIFT

• SQUEEZE

• TRIM

To apply any of these functions to a column with a long data type, first coerce the column to an acceptable data type. For example:

SQUEEZE(VARCHAR(long_varchar_column))

If a coercion function is applied to a LONG VARCHAR, LONG NVARCHAR, or LONG BYTE value that is longer than 32,000 characters or bytes, the result is truncated to 32,000 characters or bytes.

String Functions and the UTF8 Character Set

Note: For the UTF8 character set, the character data is multi-byte string and the actual number of bytes for the data could be more than the number of characters. If the output buffer for any string operation is not sufficiently large to hold the multi-byte string, the result will be truncated at a character boundary.

Date and Time Functions

Date and time functions operate on a date and time input value and return a string, numeric, or date and time value.

ADD_MONTHS

ADD_MONTHS(datetime, n)

Operand type: datetime can be an ANSIDATE, TIMESTAMP, CHAR, VARCHAR, NCHAR, or NVARCHAR; n is an integer.

Result type: Same as the first parameter

Adds the number, which represents months, to the date. If the number is negative then the date is reduced by the number of months.

Example:

SELECT ADD_MONTHS('2012-11-07',2)\g

2013-01-07

DATE_FORMAT

DATE_FORMAT(datetime, format)

TIME_FORMAT

TIME_FORMAT(datetime, format)

TIME_FORMAT is an alias for DATE_FORMAT.

Operand type: datetime is a DATE, TIME, or TIMESTAMP; format is a character string

Result type: VARCHAR

Returns datetime formatted according to the format string.

The specifiers in the following table can be used in the format string. The “%” character is required before format specifier characters. If the format specifier is inappropriate to the data type of datetime, then NULL is returned.

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (1st, 2nd, 3rd, …)
%d	Day of the month, numeric (01‑31)
%e	Day of the month, numeric (1‑31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric (four digits)
%y	Year, numeric (two digits)
%%	A literal “%” character
%x	x, for any "x" not listed above

DATE_FORMAT('2010-10-03 22:23:00', '%W %M %Y')

returns 'Sunday October 2010'

DATE_FORMAT('2007-10-03 22:23:00', '%H:%i:%s')

returns '22:23:00'

DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')

returns '4th 00 Thu 04 10 Oct 277'

DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w')

returns '22 22 10 10:23:00 PM 22:23:00 00 6'

DATE_FORMAT('1999-01-01', '%X %V')

returns '1998 52'

DATE_GMT

DATE_GMT(datetime)

Result type: any character data type

Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00.

For example, the query:

SELECT DATE_GMT('1-1-98 10:13 PM PST')

returns:

1998_01_02 06:13:00 GMT

while the query:

SELECT DATE_GMT('1-1-1998')

returns:

1998_01_01 00:00:00 GMT

DATE_PART

DATE_PART(unit,date)

Result type: INTEGER8

Returns an integer containing the specified unit component of the input date.

The following table lists valid unit parameters. A unit parameter must be specified using a quoted string (for example: 'YEAR'). The parameter is case sensitive.

Date Portion	How Unit Parameter Is Specified
Year	YEAR, YEARS, YR, YRS
Quarter	QUARTER, QUARTERS, QTR, QTRS
Month	MONTH, MONTHS, MO, MOS
Week	WEEK, WEEKS, WK, WKS
ISO-Week	ISO-WEEK, ISO-WK
Day of month	DAY, DAYS
Day of week	DAYOFWEEK, DOW
Day of year	DAYOFYEAR, DOY
Hour	HOUR, HOURS, HR, HRS
Minute	MINUTE, MINUTES, MIN, MINS
Second	SECOND, SECONDS, SES, SECS
Milliseconds	MILLISECOND
Microseconds	MICROSECOND
Nanoseconds	NANOSECOND
UNIX timestamp	EPOCH

Many of these units can also be derived using the EXTRACT function or using explicit, individual extract functions such as HOUR() or MILLISECOND().

The DATE_PART function is useful in set functions and in ensuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-2012, then:

DATE_PART('MONTH',DATE(date_field))

returns a value of 10 (representing October), and

DATE_PART('DAY',DATE(date_field))

returns a value of 23.

Months are numbered 1 to 12, starting with January. Hours are returned according to the 24-hour clock. Quarters are numbered 1 through 4.

Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. However, if you specify ISO-Week, which is ISO 8601 compliant, the week begins on Monday, but the first week is the week that has the first Thursday. The weeks are numbered 1 through 53.

Therefore, if you are using Week and the date falls before the first Monday in the current year, date_part returns 0. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and DATE_PART returns either 52 or 53.

The following table illustrates the difference between Week and ISO-Week:

Date Column	Day of Week	Week	ISO-Week
02-jan-2009	Fri	0	1
04-jan-2009	Sun	0	1
02-jan-2010	Sat	0	53
04-jan-2010	Mon	1	1
02-jan-2011	Sun	0	52
04-jan-2011	Tue	1	1
02-jan-2012	Tue	1	1
04-jan-2012	Thu	1	1

DATE_TRUNC

DATE_TRUNC(unit,date)

Operand type: date can be absolute INGRESDATE, ANSIDATE, TIME, TIMESTAMP

Result type: Same as operand type

Returns a date value truncated to the specified unit. Valid unit parameters are described under DATE_PART.

Where unit is DAY or greater, the day boundary is taken to be in the user's time zone. For example, the following query against a timestamp with time zone value:

SELECT DATE_TRUNC('DAY',TIMESTAMP '2011-02-03 16:12:13.000000000-08:00')

returns

2011-02-03 00:00:00.000000000-08:00

which retains the original time zone offset.

Use the DATE_TRUNC function to group all the dates within the same month or year, and so forth. For example:

DATE_TRUNC('MONTH',DATE('23-oct-1998 12:33'))

returns 1-oct-1998, and

DATE_TRUNC('YEAR',DATE('23-oct-1998'))

returns 1-jan-1998.

Truncation takes place in terms of calendar years and quarters (1-jan, 1-apr, 1-jun, and 1-oct).

To truncate in terms of a fiscal year, offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year (6 mos for a fiscal year beginning July 1, or 4 mos for a fiscal year beginning September 1):

DATE_TRUNC('YEAR',date+'4 mos') - '4 mos'

Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.

DAY

DAY('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the day portion of a date or timestamp.

DAY(TIMESTAMP '2006-12-15 12:30:55.1234') returns 15

DAYOFMONTH

DAYOFMONTH(datetime)

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER4

Returns the day of the month from the specified datetime value.

DAYOFMONTH(DATE '2011-02-15') returns 15

DAYOFWEEK

DAYOFWEEK(datetime [,n])

Operand type: datetime is a DATE, TIME, or TIMESTAMP; n is an integer.

Result type: INTEGER4

Returns the day of the week from the specified datetime value, where 1 = Sunday unless n is used to shift the start of week.

DAYOFWEEK(DATE '2011-02-15',4) returns 6

DAYOFWEEK(DATE '2011-02-15') returns 3

DAYOFYEAR

DAYOFYEAR(datetime)

Returns the ordinal number of day in the year, in the range 1 to 366, for datetime.

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER4

DAYOFYEAR(DATE '2011-02-04') returns 35

DOY

Same as DAYOFYEAR.

DOW

DOW(datetime)

Operand type: datetime is a DATE or TIMESTAMP

Returns the day of the week as a 3‑character string.

DOW('2011-12-25') returns Sun.

EXTRACT

EXTRACT (part FROM datetime)

Operand type: datetime can be a DATE, TIME, TIMESTAMP, or INTERVAL value expression.

Result type: INTEGER4

Extracts a particular field from a datetime value. Part specifies the field to extract.

A part parameter must be specified using a quoted string (for example: 'YEAR'). The parameter and is case sensitive.

Valid values for part are:

YEAR

Year field. Range: 0 - 9999

MONTH

Month field. Range: 1 - 12

DAY

Day field. Range: 1 - 31

HOUR

Hour field. Range: 0 - 23

MINUTE

Minute field. Range: 0 - 59

SECOND

Second field. Range: 0 - 59

MILLISECOND

Fractional seconds as milliseconds. Range: 0 - 999.

MICROSECOND

Fractional seconds as microseconds. Range: 0 - 999.

NANOSECOND

Fractional seconds as nanoseconds. Range: 0 - 999.

TIMEZONE_HOUR

Time zone hour offset. Range: ‑12 ‑ 14.

TIMEZONE_MINUTE

Time zone minute offset. Range: 0 - 59

DAYOFWEEK

Day of week with Sunday=1. Range: 1 - 7.

DOW

Day of week with Sunday=1. Range: 1 - 7.

DAYOFYEAR

Day of year. Range: 1 - 366.

DOY

Day of year. Range: 1 - 366.

WEEK

Week of year. Range: 1 - 53.

ISO_WEEK

Week of year ISO 6801.

QUARTER

Year quarter. Range: 1 - 4.

EPOCH

UNIX timestamp (number of seconds since 1-Jan-1970).

Note: The datetime value cannot be an interval type on the TIMEZONE_HOUR, TIMEZONE_MINUTE, DAYOFWEEK, DAYOFYEAR, WEEK, ISO_WEEK, QUARTER, and EPOCH functions.

Examples:

SELECT EXTRACT (YEAR FROM datecol) FROM datetable;

SELECT EXTRACT (MONTH FROM CURRENT_DATE);

SELECT EXTRACT (HOUR FROM timecol) FROM datetable;

SELECT * FROM tx WHERE EXTRACT(HOUR FROM datetable) = 17;

FROM_UNIXTIME

FROM_UNIXTIME(i)

Operand types: INTEGER4, character string

Formats UNIX timestamp as a date.

Result type: TIMESTAMP WITHOUT TIME ZONE

Returns TIMESTAMP WITHOUT TIME ZONE created from the specified integer, which must be a UNIX time (number of seconds since 1-Jan-1970).

FROM_UNIXTIME(i, format)

Result type: VARCHAR

Returns i formatted in UNIX time, according to the specified format. For valid formats, see DATE_FORMAT.

FROM_UNIXTIME(1196440219)

returns 2007-11-30 10:30:19.000000

FROM_UNIXTIME(1196440219, '%Y %D %M %h:%i:%s %x')

returns 2007 30th November 10:30:19 2007

Note: Results from this function are from a GMT offset, which may result in a value that is from '1969-12-31 12:00:00.000000' depending on the actual time zone of the server.

GMT_TIMESTAMP

GMT_TIMESTAMP(s)

Result type: Any character data type

Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is ‘yyyy_mm_dd hh:mm:ss GMT’.

For example, the query:

SELECT (GMT_TIMESTAMP (1234567890))

returns:

2009_02_13 23:31:30 GMT

while the query:

(II_TIMEZONE_NAME = AUSTRALIA-BRISBANE)

SELECT date(GMT_TIMESTAMP (1234567890))

returns:

14-feb-2009 09:31:30

HOUR

HOUR('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the hour portion of a time or timestamp.

HOUR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12

INTERVAL

INTERVAL(unit,date_interval)

Result type: FLOAT

Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the month, quarter, and year specifications.

For example, the query:

SELECT(INTERVAL('days', '5 years'))

returns:

1826.213

INTERVAL_DIFF

INTERVAL_DIFF('datetime1', 'datetime2')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTERVAL YEAR TO MONTH

Returns the difference between datetime1 and datetime2 expressed as an INTERVAL YEAR TO MONTH.

This complements subtraction of TIMESTAMPs that yields an INTERVAL DAY TO SECOND and subtraction of ANSIDATEs that yields an integer.

INTERVAL_DIFF('2010-1-1', '2015-3-1') returns 5-2

ISDST

ISDST(date)

Returns true if date occurs during Daylight Saving Time in its time zone.

LAST_DAY

LAST_DAY(datetime)

Operand type: DATE, TIMESTAMP

Result type: DATE or TIMESTAMP depending on input

Returns the last day of the month in which the specified date or timestamp falls. Returns NULL if the argument is invalid.

LAST_DAY(DATE '2003-02-05')

returns 2003-02-28

LAST_DAY(DATE '2004-02-05')

returns 2004-02-29

LAST_DAY(TIMESTAMP '2004-01-01 01:01:01')

returns 2004-01-31

LAST_DAY(DATE '2003-03-32')

returns NULL

MICROSECOND

MICROSECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER4

Extracts the fractions of seconds portion of a time or timestamp as microseconds.

MICROSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400

MILLISECOND

MILLISECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER4

Extracts the fractions of seconds portion of a time or timestamp as milliseconds.

MILLISECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123

MINUTE

MINUTE('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the minute portion of a time or timestamp.

MINUTE(TIMESTAMP '2006-12-15 12:30:55.1234') returns 30

MONTH

MONTH('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the month portion of a date or timestamp.

MONTH(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12

MONTHS_BETWEEN

MONTH_BETWEEN(date1, date2)

Operand type: DATE, TIMESTAMP

Result type: FLOAT8

Returns the number of months between date1 and date2, positive if date2 precedes date1 and negative if date1 precedes date2. If the day of date1 is the same as the day in date2 or both dates are the last days of months, then the result is a whole number of months; otherwise, any time portion is also taken into consideration in the difference, where days are treated as 1/31 of a month.

SELECT MONTHS_BETWEEN(DATE'2010-06-15', DATE'2011-06-15');

-12.000

SELECT MONTHS_BETWEEN(DATE'2011-06-15', DATE'2011-05-15');

1.000

SELECT MONTHS_BETWEEN(DATE'2011-06-30', DATE'2011-05-31');

1.000

SELECT MONTHS_BETWEEN(DATE'2011-06-15', DATE'2011-06-01');

0.452

NANOSECOND

NANOSECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER4

Extracts the fractions of seconds portion of a time or timestamp as nanoseconds.

NANOSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400000

QUARTER

QUARTER('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the quarter of the calendar year that corresponds to the date or timestamp. Quarters are numbered 1 through 4.

QUARTER(TIMESTAMP '2006-12-15 12:30:55.1234') returns 4

SECOND

SECOND('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the second portion of a time or timestamp.

SECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 55

STR_TO_DATE

(X100 only)

STR_TO_DATE(str,format)

Operand type: str and format are character strings

Result type: DATE, TIME, or TIMESTAMP

Returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the format string contains only a date or time part. If the DATE, TIME, or DATETIME value extracted from string str is illegal, the function returns NULL and produces a warning. This function is the inverse of the DATE_FORMAT() function.

The format string can contain literal characters and format specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. For the specifiers that can be used in format, see DATE_FORMAT.

teststr(str VARCHAR(20));

INSERT INTO teststr VALUES('01,6,2012');

SELECT STR_TO_DATE(str,'%d,%m,%Y') FROM teststr;

returns

2012-06-01

TIMESTAMPADD

TIMESTAMPADD(interval, n, datetime)

Operand type: Integer, and DATE, TIME, TIMESTAMP

Result type: DATE, TIME, or TIMESTAMP

Returns the datetime after adding the specified number of intervals where:

interval is a keyword from the list: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.

n is an integer expression. The value can be positive or negative as needed.

datetime is a column or a datetime expression that can be a DATE, TIMESTAMP, or TIME value.

TIMESTAMPADD(YEAR, 5, '2010-10-05') returns 2015-10-5

TIMESTAMPDIFF

TIMESTAMPDIFF(interval, datetime1, datetime2)

Operand type: Integer, and DATE, TIME, TIMESTAMP

Result type: Integer type

Returns the integer number of intervals between the two datetimes where:

interval is one of the following keywords: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.

datetime1, datetime2 are columns or datetime expressions whose difference is to be determined in terms of the specific interval, and where datetime1 is a start time, and datetime2 is an end time.

The result is an integer value which can also be negative if datetime1 chronologically follows datetime2.

TIMESTAMPDIFF(YEAR, '2012-01-01', '2008-01-01') returns ‑4

TRUNC

TRUNC(datetime [,str-interval])

TRUNCATE(datetime [,str-interval])

Operand type: datetime is a DATE, TIME, or TIMESTAMP.

Result type: DATE, TIME, or TIMESTAMP

Returns datetime truncated to the unit specified by str-interval. If str-interval is omitted, then datetime is truncated to the nearest day.

The str-interval can be any of the following:

Formats	Description
CC CENTURY CENTURIES	Century
D DY DAY	Starting day of week
DD DDD DAYS J	Day
HH HH12 HR HRS HOUR HOURS	Hour
I IY IYY IYYYY ISO-YR ISO-YRS ISO-YEAR ISO-YEARS	ISO year boundary
IW ISO-WK ISO-WEEK	Same day of week as 1st of ISO year
MI MIN MINS MINUTE MINUTES	Minute
MM MO MON MONTH MONTHS RM MOS	Month
Q QTR QTRS QUARTER QUARTERS	Quarter
W	Same day of week as 1st of month
WW	Same day of week as 1st of year
Y YEAR YY YYY YYYY	Year

The precision of the result is the default precision for the input data type. For example, if TRUNC operates on a TIME column, the precision returned is 0; if TIMESTAMP, then 6.

Examples:

SELECT TRUNC('2013-10-11','CC')\g

2000-01-01

SELECT TRUNC('2013-10-11 23:40:10.123456','DD')\g

2013-10-11 00:00:00.000000

SELECT TRUNC('23:40:10.123456','MIN')\g

23:40:00

SELECT TRUNC('2013-10-11 23:40:10.123456')\g

2013-10-11 00:00:00.000000

UNIX_TIMESTAMP

UNIX_TIMESTAMP()

UNIX_TIMESTAMP([datetime])

Operand type: DATE, TIME, or TIMESTAMP

Result type: INTEGER4

Returns a UNIX timestamp (number of seconds since 1 Jan 1970) for the current time (if no argument is specified) or for the specified date.

UNIX_TIMESTAMP(TIMESTAMP '2007-11-30 10:30:19') returns 1196440219

Note: This function returns a valid result up to the year 2038 because the result is always an INTEGER4 value in the range 1 though MAX_I4.

WEEK

WEEK('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Returns the number of the week of the year that the date or timestamp refers to. Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. Weeks are numbered 1 to 53.

WEEK('2006-12-15 12:30:55.1234') returns 50

WEEK_ISO

WEEKISO('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER1

Extracts the number of the week of the year that the date or timestamp refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begins on Monday, but the first week is the week that has the first Thursday of the year. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and date_part returns either 52 or 53.

WEEK_ISO is equivalent to WEEK() function with mode 3.

WEEK_ISO(TIMESTAMP '2006-12-15 12:30:55.1234') returns 50

YEAR

YEAR('datetime')

Operand type: DATE, TIME, TIMESTAMP

Result type: INTEGER2

Extracts the year portion of a date or timestamp.

YEAR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 2006

_DATE

_DATE(s)

Result type: Any character data type

Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy.

For example, the query:

SELECT _DATE(123456)

returns:

2-jan-70

Note: This function formats a leading space for day values less than 10.

_DATE4

_DATE4(s)

Result type: Any character data type

Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting.

For example, with II_DATE_FORMAT set to US, the query:

SELECT _DATE4(123456)

returns:

02-jan-1970

while with II_DATE_FORMAT set to MULTINATIONAL, the query:

SELECT _DATE4(123456)

returns:

02/01/1970

_TIME

_TIME(s)

Result type: Any character data type

Returns a five-character string giving the time s seconds after January 1, 1970 GMT, which is then adjusted for your local time zone. The output format is hh:mm (seconds are truncated).

For example, the query:

SELECT _TIME(123456)

returns the value 02:17 for the America-Los_Angeles time zone.

Bitwise Functions

Bitwise functions operate from right to left, with shorter operands padded with binary zeroes to the left.

The bitwise functions are:

BIT_ADD

BIT_ADD(expr, expr)

Operand type: BYTE

Result type: Same as operands

Returns the logical ADD of two byte operands; any overflow is disregarded.

“Logical add” is adding the binary content of the two operands as if they were unsigned binary integers. If the sum does not fit in the result field, the overflow is disregarded and the rest is retained.

For example: C8 + 5A = 0122.

SELECT HEX(BIT_ADD(BYTE(x'C8'), BYTE(x'5A')))

Result is hex 22 because the 01 is dropped as part of the carry over.

BIT_AND

BIT_AND(expr, expr)

Operand type: BYTE, or IPV4 or IPV6 address

Result type: Same as operands

Returns the logical AND of one or more operands.

If two bits are 1, the answer is 1; otherwise the answer is 0.

SELECT BIT_AND(IPV4('255.255.255.0'),IPV4('172.16.254.1'))

returns (as an IPv4)

172.16.254.0

BIT_NOT

BIT_NOT(expr)

Operand type: Single BYTE, or IPV4 or IPV6 address

Result type: Same as operand

Returns the logical NOT of the operand.

SELECT BIT_NOT(IPV4('172.16.254.1'))

returns (as an IPv4):

83.239.1.254

BIT_OR

BIT_OR(expr, expr)

Operand type: BYTE, or IPV4 or IPV6 address

Result type: Same as operands

Returns the logical OR of one or more operands.

If either or both bits are 1, the answer is 1.

SELECT BIT_OR(IPV4('255.255.255.0'),ipv4('172.16.254.1'))\g

returns (as an IPv4):

255.255.255.1

BIT_XOR

BIT_XOR(expr, expr)

Operand type: BYTE, or IPV4 or IPV6 address

Result type: Same as operands

Returns the logical XOR of one or more operands.

If either, but not both, bits is 1, the answer is 1; otherwise the answer is 0.

SELECT BIT_XOR(IPV4('255.255.255.0'),IPV4('172.16.254.1'))

returns (as an IPv4)

83.239.1.1

INTEXTRACT

INTEXTRACT(byte, n)

Operand types: BYTE, INTEGER4

Result type: INTEGER4

Returns the nth byte as an integer. n is an integer field. Similar to the CHAREXTRACT string function.

INTEXTRACT(X'0A020C04', 3)

returns 12, which is the integer value for the third byte '0C'.

If n is less than 1 or larger than the number of bytes available, then a 0 is returned.

INTEXTRACT(X'0A020C04', -2)

INTEXTRACT(X'0A020C04', 5)

both return 0.

Hashing Functions

Hashing functions generate a fixed length “hash” value for given data. They are useful for summarizing a relatively large data item into a small fixed length representation that usually will be distinct from hashes of dissimilar data. Uniqueness is not guaranteed but the chances of two dissimilar items having the same hash value is very low.

HASH

HASH(expr)

Operand type: Any except long types

Result type: INTEGER4

Generates a four-byte numeric value from expressions of all data types except long types. The implicit size for the expression can affect the result.

SELECT HASH(1), HASH(int1(1)), HASH(int2(1)), HASH(int4(1))\g

returns the following single row:

Col1 Col2 Col3 Col4

--------------------------------------------------

-1489118143 1526341860 ‑1489118143 1711753094

Note: Because the constant 1 is implicitly a short integer, only the return values for HASH(1) and HASH(int2(1)) match. For the remaining columns, the difference in the number of bytes holding the integer leads to a different hash value. Also, the generated hash value is not guaranteed unique, even if the input values are unique.

SHA1SUM

SHA1SUM(expr)

Operand type: Any

Result type: CHAR(40)

Returns the SHA-1 checksum for the input data.

SHA224SUM

SHA224SUM(expr)

Operand type: Any

Result type: CHAR(56)

Returns the SHA-224 checksum for the input data.

SHA256SUM

SHA256SUM(expr)

Operand type: Any

Result type: CHAR(64)

Returns the SHA-256 checksum for the input data.

SHA384SUM

SHA384SUM(expr)

Operand type: Any

Result type: CHAR(96)

Returns the SHA-384 checksum for the input data.

SHA512SUM

SHA512SUM(expr)

Operand type: Any

Result type: CHAR(128)

Returns the SHA-512 checksum for the input data.

Random Number Functions

The random number function is used to generate random values. Use the following statement to set the beginning value for the random functions:

[EXEC SQL] SET RANDOM_SEED [value]

The seed value can be any integer. There is a global seed value and local seed values. The global value is used until you issue SET RANDOM_SEED, which changes the value of the local seed. Once changed, the local seed is used for the whole session. If you are using the global seed value, the seed is changed whenever a random function executes. This means that other users issuing random calls enhance the “randomness” of the returned value.

If you omit the value, Actian X multiplies the process ID by the number of seconds past 1/1/2016 until now. This value generates a random starting point. You can use value to run a regression test from a static start and get identical results.

The random number functions are:

RAND() or RAND(integer)

Result type: float4

Returns a random floating point value between 0.0 and 1.0. If the optional integer parameter is specified, the value is used as a random seed. This function is the same as the RANDOM() function.

RANDOM()

Returns a random integer based on a seed value.

RANDOMF()

Returns a random float based on a seed value between 0 and 1. This is slower than RANDOM, but produces a more random number.

RANDOM(l,h)

Returns a random integer within the specified range (that is, l <= x <= h).

RANDOMF(l,h)

Passing two integer values generates an integer result within the specified range; passing two floats generates a float within the specified range; passing an int and a float causes them to be coerced to an int and generates an integer result within the specified range (that is, l <= x <= h).

SRAND(integer)

Sets the random seed using the specified integer value. Always returns 0. It performs the same function as the SET RANDOM_SEED statement.

IP Network Address Functions

IP network address functions operate on an IPV4 or IPV6 data type or string representing an IPv4 or IPv6 address and return a string in dotted quad or colon-hex format or an IPv4 or IPv6 binary.

INET_NTOP

INET_NTOP(expr)

Operand type: IPV4 or IPV6

Result type: Dotted quad string or colon-hex string, depending on input type

Returns a dotted quad string if the input type is IPV4 or is IPV6 and an IPV4-mapped address.

Returns an IPv6 address in rfc5952 style (zero suppressed, shortened, lowercase) if the input type is IPV6 and is not an IPv4-mapped address.

SELECT INET_NTOP(INET_PTON('172.16.254.1'))

returns

'172.16.254.1'

INET_PTON

INET_PTON(expr)

IPV4(expr)

Operand type: A string representing an IPv4 network address or an IPv4-mapped IPv6 address

Result type: IPv4 binary

Converts a string in standard IPv4 dotted-quad notation to its IPv4 binary equivalent.

The expression can also be an IPv4-mapped IPv6 address in the form:

"::ffff:xxx:xxx" or "::ffff:n.n.n.n"

and zero-expanded or uppercased variants of the same. An error occurs if the input is an IPv6 style string that does not represent an IPv4-mapped address.

SELECT INET_PTON('172.16.254.1');

returns the following (using hexadeximal representation):

AC10FE01

INET6_NTOP

INET6_NTOP(expr)

Operand type: IPV4 or IPV6

Result type: Colon-hex string

Returns an IPv6 address in rfc5952 style (zero suppressed, shortened, lowercase)

SELECT INET6_NTOP(INET6_PTON('172.16.254.1'))

returns

'::ffff:ac10:fe01'

INET6_PTON

INET6_PTON(expr)

IPV6(expr)

Operand type: A string representing an IPv4 or IPv6 network address

Result type: IPv6 binary

Converts a string in standard hex-colon notation to an IPv6 binary equivalent. Also converts an IPv4 address in either ordinary dotted-quad or "::ffff:n.n.n.n" mixed quad notation to an IPv4-mapped address ::ffff:n.n.n.n.

The input can be leading-zero-suppressed. Use one “::” to replace the longest sequence of all-zero fields and use lowercase letters. The function also accepts uppercase hexits as input.

SELECT INET6_PTON('2001:0DB8:AC10:FE01:0000:0000:0000:0000')

returns the following (using hexadecimal representation):

20010DB8AC10FE010000000000000000

Last modified date: 04/03/2024