Actian X 12.0 | Numeric Functions

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

Was this helpful?

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(str1, str2 [, len])

Result type: FLOAT4

Calculates the Jaro_Winkler similarity between two strings str1 and str2 and returns the float value between 0 and 1. Here, 0.0 means no similarities and 1.0 means the strings are identical. If the optional length parameter is omitted, the strings must be of type VARCHAR and the comparison is limited to the first 60 characters for longer strings.

If the length parameter is specified, the strings can be either VARCHAR or LONG VARCHAR and the comparison will apply to the user specified length. If the specified length is zero or greater than the length of both strings, the full strings are compared.

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

SELECT JARO_WINKLER(‘abcd1’, ’abcd2’, 4) returns 1.0

SELECT JARO_WINKLER(‘abcd1’, ’abcd2’, 0) returns 0.920

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'

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 LENGTHB (col1) returns 7

SELECT OCTET_LENGTH (col2) returns 10

SELECT LENGTHB (col2) returns 10

SELECT LENGTH (col2) from octet \g returns 7

Note: The CLOB (long varchar) and NCLOB (long nvarchar) data types are not supported for the LENGTHB function and OCTET_LENGTH function.

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.

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 also be used and this result is returned when CHARSET is set to UTF8:

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È'

LIKE_REGEX

SUBSTRING(s LIKE_REGEX pattern [FLAG flag])

Returns the substring from the subject that best matches the pattern.

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

SELECT SUBSTRING (city LIKE_REGEX '.*\".r[ef].*\"') FROM cities;

SELECT SUBSTRING (city LIKE_REGEX '.*\".*EN.*\"' FLAG 'i') FROM cities;

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.

create table 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.

MD5_HEX (X100 ONLY)

MD5_HEX(expr)

Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8

Result type: CHAR(32)

Calculates the MD5 (Message-Digest Algorithm 5) 128-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).

Returns the hash value as a lowercase hexadecimal string.

SHA1_HEX (X100 ONLY)

SHA1_HEX(expr)

Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8

Result type: CHAR(40)

Calculates the SHA-1 (Secure Hash Algorithm 1) 160-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).

Returns the hash value as a lowercase hexadecimal string.

SHA224_HEX (X100 ONLY)

SHA224_HEX(expr)

Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8

Result type: CHAR(56)

Calculates the SHA-2 (Secure Hash Algorithm 2) 224-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).

Returns the hash value as a lowercase hexadecimal string.

SHA256_HEX (X100 ONLY)

SHA256_HEX(expr)

Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8

Result type: CHAR(64)

Calculates the SHA-2 (Secure Hash Algorithm 2) 256-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).

Returns the hash value as lowercase hexadecimal string.

SHA384_HEX (X100 ONLY)

SHA384_HEX(expr)

Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8

Result type: CHAR(96)

Calculates the SHA-2 (Secure Hash Algorithm 2) 384-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).

Returns the hash value as lowercase hexadecimal string.

SHA512_HEX (X100 ONLY)

SHA512_HEX(expr)

Operand type: CHAR, VARCHAR, NCHAR, NVARCHAR, INTEGER1, INTEGER2, INTEGER4, INTEGER8

Result type: CHAR(128)

Calculates the SHA-2 (Secure Hash Algorithm 2) 512-bit hash value (message digest) of expr with respect to its binary representation as given by HEX(expr).

Returns the hash value as lowercase hexadecimal string.

MD5SUM (HEAP ONLY)

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'

SHA1SUM (HEAP ONLY)

SHA1SUM(expr)

Operand type: Any

Result type: CHAR(40)

Returns the SHA-1 checksum for the input data.

SHA224SUM (HEAP ONLY)

SHA224SUM(expr)

Operand type: Any

Result type: CHAR(56)

Returns the SHA-224 checksum for the input data.

SHA256SUM (HEAP ONLY)

SHA256SUM(expr)

Operand type: Any

Result type: CHAR(64)

Returns the SHA-256 checksum for the input data.

SHA384SUM (HEAP ONLY)

SHA384SUM(expr)

Operand type: Any

Result type: CHAR(96)

Returns the SHA-384 checksum for the input data.

SHA512SUM (HEAP ONLY)

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: 08/14/2024