Other functions in this section operate specifically on arrays or sets, as indicated on the individual reference pages. Array functions operate on both native array values and array values in external tables.

Notes

Arrays are 0-indexed. The first element's ordinal position in 0, second is 1, and so on. Indexes are not meaningful for sets.
Unless otherwise stated, functions operate on one-dimensional (1D) collections only. To use multidimensional arrays, you must first dereference to a 1D array type. Sets can only be one-dimensional.

1.1 - APPLY_AVG

Returns the average of all elements in a with numeric values.

Returns the average of all elements in a collection (array or set) with numeric values.

Behavior type

Immutable

Syntax

APPLY_AVG(collection)

Arguments

collection: Target collection

Null-handling

The following cases return NULL:

if the input collection is NULL
if the input collection contains only null values
if the input collection is empty

If the input collection contains a mix of null and non-null elements, only the non-null values are considered in the calculation of the average.

Examples

=> SELECT apply_avg(ARRAY[1,2.4,5,6]);
apply_avg
-----------
3.6
(1 row)

1.2 - APPLY_COUNT (ARRAY_COUNT)

Returns the total number of non-null elements in a.

Returns the total number of non-null elements in a collection (array or set). To count all elements including nulls, use APPLY_COUNT_ELEMENTS (ARRAY_LENGTH).

Behavior type

Immutable

Syntax

APPLY_COUNT(collection)

ARRAY_COUNT is a synonym of APPLY_COUNT.

Arguments

collection: Target collection

Null-handling

Null values are not included in the count.

Examples

The array in this example contains six elements, one of which is null:

=> SELECT apply_count(ARRAY[1,NULL,3,7,8,5]);
apply_count
-------------
5
(1 row)

1.3 - APPLY_COUNT_ELEMENTS (ARRAY_LENGTH)

Returns the total number of elements in a , including NULLs.

Returns the total number of elements in a collection (array or set), including NULLs. To count only non-null values, use APPLY_COUNT (ARRAY_COUNT).

Behavior type

Immutable

Syntax

APPLY_COUNT_ELEMENTS(collection)

ARRAY_LENGTH is a synonym of APPLY_COUNT_ELEMENTS.

Arguments

collection: Target collection

Null-handling

This function counts all members, including nulls.

An empty collection (ARRAY[] or SET[]) has a length of 0. A collection containing a single null (ARRAY[null] or SET[null]) has a length of 1.

Examples

The following array has six elements including one null:

=> SELECT apply_count_elements(ARRAY[1,NULL,3,7,8,5]);
apply_count_elements
---------------------
                   6
(1 row)

As the previous example shows, a null element is an element. Thus, an array containing only a null element has one element:

=> SELECT apply_count_elements(ARRAY[null]);
apply_count_elements
---------------------
                   1
(1 row)

A set does not contain duplicates. If you construct a set and pass it directly to this function, the result could differ from the number of inputs:

=> SELECT apply_count_elements(SET[1,1,3]);
apply_count_elements
---------------------
                   2
(1 row)

1.4 - APPLY_MAX

Returns the largest non-null element in a.

Returns the largest non-null element in a collection (array or set). This function is similar to the MAX [aggregate] function; APPLY_MAX operates on elements of a collection and MAX operates on an expression such as a column selection.

Behavior type

Immutable

Syntax

APPLY_MAX(collection)

Arguments

collection: Target collection

Null-handling

This function ignores null elements. If all elements are null or the collection is empty, this function returns null.

Examples

=> SELECT apply_max(ARRAY[1,3.4,15]);
apply_max
-----------
     15.0
(1 row)

1.5 - APPLY_MIN

Returns the smallest non-null element in a.

Returns the smallest non-null element in a collection (array or set). This function is similar to the MIN [aggregate] function; APPLY_MIN operates on elements of a collection and MIN operates on an expression such as a column selection.

Behavior type

Immutable

Syntax

APPLY_MIN(collection)

Arguments

collection: Target collection

Null-handling

This function ignores null elements. If all elements are null or the collection is empty, this function returns null.

Examples

=> SELECT apply_min(ARRAY[1,3.4,15]);
apply_min
-----------
       1.0
(1 row)

1.6 - APPLY_SUM

Computes the sum of all elements in a of numeric values (INTEGER, FLOAT, NUMERIC, or INTERVAL).

Computes the sum of all elements in a collection (array or set) of numeric values (INTEGER, FLOAT, NUMERIC, or INTERVAL).

Behavior type

Immutable

Syntax

APPLY_SUM(collection)

Arguments

collection: Target collection

Null-handling

The following cases return NULL:

if the input collection is NULL
if the input collection contains only null values
if the input collection is empty

Examples

=> SELECT apply_sum(ARRAY[12.5,3,4,1]);
apply_sum
-----------
      20.5
(1 row)

1.7 - ARRAY_CAT

Concatenates two arrays of the same element type and dimensionality.

Concatenates two arrays of the same element type and dimensionality. For example, ROW elements must have the same fields.

If the inputs are both bounded, the bound for the result is the sum of the bounds of the inputs.

If any input is unbounded, the result is unbounded with a binary size that is the sum of the sizes of the inputs.

Behavior type

Immutable

Syntax

ARRAY_CAT(array1,array2)

Arguments

array1, array2: Arrays of matching dimensionality and element type

Null-handling

If either input is NULL, the function returns NULL.

Examples

Types are coerced if necessary, as shown in the second example.

=> SELECT array_cat(ARRAY[1,2], ARRAY[3,4,5]);
array_cat
-----------------------
[1,2,3,4,5]
(1 row)

=> SELECT array_cat(ARRAY[1,2], ARRAY[3,4,5.0]);
array_cat
-----------------------
["1.0","2.0","3.0","4.0","5.0"]
(1 row)

1.8 - ARRAY_CONTAINS

Returns true if the specified element is found in the array and false if not.

Returns true if the specified element is found in the array and false if not. Both arguments must be non-null, but the array may be empty.

Deprecated

This function has been renamed to CONTAINS.

1.9 - ARRAY_DIMS

Returns the dimensionality of the input array.

Behavior type

Immutable

Syntax

ARRAY_DIMS(array)

Arguments

array: Target array

Examples

=> SELECT array_dims(ARRAY[[1,2],[2,3]]);
array_dims
------------
        2
(1 row)

1.10 - ARRAY_FIND

Returns the ordinal position of a specified element in an array, or -1 if not found.

Returns the ordinal position of a specified element in an array, or -1 if not found. This function uses null-safe equality checks when testing elements.

Behavior type

Immutable

Syntax

ARRAY_FIND(array, { value | lambda-expression })

Arguments

array: Target array.
value: Value to search for; type must match or be coercible to the element type of the array.
lambda-expression: Lambda function to apply to each element. The function must return a Boolean value. The first argument to the function is the element, and the optional second element is the index of the element.

Examples

The function returns the first occurrence of the specified element. However, nothing ensures that value is unique in the array:

=> SELECT array_find(ARRAY[1,2,7,5,7],7);
 array_find
------------
          2
(1 row)

The function returns -1 if the specified element is not found:

=> SELECT array_find(ARRAY[1,3,5,7],4);
array_find
------------
        -1
(1 row)

You can search for complex element types:

=> SELECT ARRAY_FIND(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]],
                     ARRAY[1,2,3]);
 ARRAY_FIND
------------
          0
(1 row)

=> SELECT ARRAY_FIND(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]],
                     ARRAY[1,null,4]);
 ARRAY_FIND
------------
          1
(1 row)

The second example, comparing arrays with null elements, finds a match because ARRAY_FIND uses a null-safe equality check when evaluating elements.

Lambdas

Consider a table of departments where each department has an array of ROW elements representing employees. The following example searches for a specific employee name in those records. The results show that Alice works (or has worked) for two departments:

=> SELECT deptID, ARRAY_FIND(employees, e -> e.name = 'Alice Adams') AS 'has_alice'
   FROM departments;
 deptID | has_alice
--------+-----------
      1 |         0
      2 |        -1
      3 |         0
(3 rows)

In the following example, each person in the table has an array of email addresses, and the function locates fake addresses. The function takes one argument, the array element to test, and calls a regular-expression function that returns a Boolean:

=> SELECT name, ARRAY_FIND(email, e -> REGEXP_LIKE(e,'example.com','i'))
                AS 'example.com'
   FROM people;
      name      | example.com
----------------+-------------
 Elaine Jackson |          -1
 Frank Adams    |           0
 Lee Jones      |          -1
 M Smith        |           0
(4 rows)

1.11 - CONTAINS

Returns true if the specified element is found in the collection and false if not.

Returns true if the specified element is found in the collection and false if not. This function uses null-safe equality checks when testing elements.

Behavior type

Immutable

Syntax

CONTAINS(collection, { value | lambda-expression })

Arguments

collection: Target collection (ARRAY or SET).
value: Value to search for; type must match or be coercible to the element type of the collection.
lambda-expression: Lambda function to apply to each element. The function must return a Boolean value. The first argument to the function is the element, and the optional second element is the index of the element.

Examples

=> SELECT CONTAINS(SET[1,2,3,4],2);
 contains
----------
t
(1 row)

You can search for NULL as an element value:

=> SELECT CONTAINS(ARRAY[1,null,2],null);
 contains
----------
 t
(1 row)

You can search for complex element types:

=> SELECT CONTAINS(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]],
                   ARRAY[1,2,3]);
 CONTAINS
----------
 t
(1 row)

=> SELECT CONTAINS(ARRAY[ARRAY[1,2,3],ARRAY[1,null,4]],
                   ARRAY[1,null,4]);
 CONTAINS
----------
 t
(1 row)

The second example, comparing arrays with null elements, returns true because CONTAINS uses a null-safe equality check when evaluating elements.

In the following example, the orders table has the following definition:

=> CREATE EXTERNAL TABLE orders(
  orderid int,
  accountid int,
  shipments Array[
    ROW(
      shipid int,
      address ROW(
        street varchar,
        city varchar,
        zip int
      ),
      shipdate date
    )
  ]
 ) AS COPY FROM '...' PARQUET;

The following query tests for a specific order. When passing a ROW literal as the second argument, cast any ambiguous fields to ensure type matches:

=> SELECT CONTAINS(shipments,
            ROW(1,ROW('911 San Marcos St'::VARCHAR,
                  'Austin'::VARCHAR, 73344),
            '2020-11-05'::DATE))
   FROM orders;
 CONTAINS
----------
 t
 f
 f
(3 rows)

Lambdas

Consider a table of departments where each department has an array of ROW elements representing employees. The following query finds departments with early hires (low employee IDs):

=> SELECT deptID FROM departments
   WHERE CONTAINS(employees, e -> e.id < 20);
 deptID
--------
      1
      3
(2 rows)

In the following example, a schedules table includes an array of events, where each event is a ROW with several fields:

=> CREATE TABLE schedules
       (guest VARCHAR,
       events ARRAY[ROW(e_date DATE, e_name VARCHAR, price NUMERIC(8,2))]);

You can use the CONTAINS function with a lambda expression to find people who have more than one event on the same day. The second argument, idx, is the index of the current element:

=> SELECT guest FROM schedules
WHERE CONTAINS(events, (e, idx) ->
                       (idx < ARRAY_LENGTH(events) - 1)
                       AND (e.e_date = events[idx + 1].e_date));
    guest
-------------
 Alice Adams
(1 row)

1.12 - EXPLODE

Expands the elements of one or more collection columns (ARRAY or SET) into individual table rows, one row per element.

Expands the elements of one or more collection columns (ARRAY or SET) into individual table rows, one row per element. For each exploded collection, the results include two columns, one for the element index, and one for the value at that position. If the function explodes a single collection, these columns are named position and value by default. If the function explodes two or more collections, the columns for each collection are named pos_column-name and val_column-name. You can use an AS clause in the SELECT to change these column names.

EXPLODE is similar to UNNEST, which returns values but not positions.

By default, EXPLODE requires an OVER clause. If you set the skip_partitioning parameter to true, an OVER clause is not required and is ignored if present.

Behavior type

Immutable

Syntax

EXPLODE (column[,...] [USING PARAMETERS param=value])
[ OVER ( [window-partition-clause] ) ]

Arguments

column: Column in the table being queried. You must specify at least as many collection columns as the value of the explode_count parameter. Columns that are not collections are passed through without modification.
Passthrough columns are not needed if skip_partitioning is true.
OVER(...): How to partition and sort input data. The input data is the result set that the query returns after it evaluates FROM, WHERE, GROUP BY, and HAVING clauses. For EXPLODE, use OVER() or OVER(PARTITION BEST).
This clause is ignored if skip_partitioning is true.

Parameters

explode_count: The number of collection columns to explode. The function checks each column, up to this value, and either explodes it if is a collection or passes it through if it is not a collection or if this limit has been reached. If the value of explode_count is greater than the number of collection columns specified, the function returns an error.
Default: 1
skip_partitioning: Whether to skip partitioning and ignore the OVER clause if present. EXPLODE translates a single row of input into multiple rows of output, one per collection element. There is, therefore, usually no benefit to partitioning the input first. Skipping partitioning can help a query avoid an expensive sort or merge operation. Even so, setting this parameter can negatively affect performance in rare cases.
Default: false

Null-handling

This function expands each element in a collection into a row, including null elements. If the input column is NULL or an empty collection, the function produces no rows for that column:

=> SELECT EXPLODE(ARRAY[1,2,null,4]) OVER();
 position | value
----------+-------
        0 |     1
        1 |     2
        2 |
        3 |     4
(4 rows)

=> SELECT EXPLODE(ARRAY[]::ARRAY[INT]) OVER();
 position | value
----------+-------
(0 rows)

=> SELECT EXPLODE(NULL::ARRAY[INT]) OVER();
 position | value
----------+-------
(0 rows)

Joining on results

To use JOIN with this function you must set the skip_partitioning parameter, either in the function call or as a session parameter.

You can use the output of this function as if it were a relation by using CROSS JOIN or LEFT JOIN LATERAL in a query. Other JOIN types are not supported.

Consider the following table of students and exam scores:

=> SELECT * FROM tests;
 student |    scores     |    questions
---------+---------------+-----------------
 Bob     | [92,78,79]    | [20,20,100]
 Lee     |               |
 Pat     | []            | []
 Sam     | [97,98,85]    | [20,20,100]
 Tom     | [68,75,82,91] | [20,20,100,100]
(5 rows)

The following query finds the best test scores across all students who have scores:

=> ALTER SESSION SET UDPARAMETER FOR ComplexTypesLib skip_partitioning = true;

=> SELECT student, score FROM tests
   CROSS JOIN EXPLODE(scores) AS t (pos, score)
   ORDER BY score DESC;
 student | score
---------+-------
 Sam     |    98
 Sam     |    97
 Bob     |    92
 Tom     |    91
 Sam     |    85
 Tom     |    82
 Bob     |    79
 Bob     |    78
 Tom     |    75
 Tom     |    68
(10 rows)

The following query returns maximum and average per-question scores, considering both the exam score and the number of questions:

=> SELECT student, MAX(score/qcount), AVG(score/qcount) FROM tests
   CROSS JOIN EXPLODE(scores, questions USING PARAMETERS explode_count=2)
      AS t(pos_s, score, pos_q, qcount)
   GROUP BY student;
 student |         MAX          |       AVG
---------+----------------------+------------------
 Bob     | 4.600000000000000000 | 3.04333333333333
 Sam     | 4.900000000000000000 | 3.42222222222222
 Tom     | 4.550000000000000000 |             2.37
(3 rows)

These queries produce results for three of the five students. One student has a null value for scores and another has an empty array. These rows are not included in the function's output.

To include null and empty arrays in output, use LEFT JOIN LATERAL instead of CROSS JOIN:

=> SELECT student, MIN(score), AVG(score) FROM tests
LEFT JOIN LATERAL EXPLODE(scores) AS t (pos, score)
GROUP BY student;
 student | MIN |       AVG
---------+-----+------------------
 Bob     |  78 |               83
 Lee     |     |
 Pat     |     |
 Sam     |  85 | 93.3333333333333
 Tom     |  68 |               79
(5 rows)

The LATERAL keyword is required with LEFT JOIN. It is optional for CROSS JOIN.

Examples

Consider an orders table with the following contents:

=> SELECT orderkey, custkey, prodkey, orderprices, email_addrs
   FROM orders LIMIT 5;
  orderkey  | custkey |                    prodkey                    |            orderprices            |                                                  email_addrs
------------+---------+-----------------------------------------------+-----------------------------------+----------------------------------------------------------------------------------------------------------------
 113-341987 |  342799 | ["MG-7190 ","VA-4028 ","EH-1247 ","MS-7018 "] | ["60.00","67.00","22.00","14.99"] | ["bob@example,com","robert.jones@example.com"]
 111-952000 |  342845 | ["ID-2586 ","IC-9010 ","MH-2401 ","JC-1905 "] | ["22.00","35.00",null,"12.00"]    | ["br92@cs.example.edu"]
 111-345634 |  342536 | ["RS-0731 ","SJ-2021 "]                       | ["50.00",null]                    | [null]
 113-965086 |  342176 | ["GW-1808 "]                                  | ["108.00"]                        | ["joe.smith@example.com"]
 111-335121 |  342321 | ["TF-3556 "]                                  | ["50.00"]                         | ["789123@example-isp.com","alexjohnson@example.com","monica@eng.example.com","sara@johnson.example.name",null]
(5 rows)

The following query explodes the order prices for a single customer. The other two columns are passed through and are repeated for each returned row:

=> SELECT EXPLODE(orderprices, custkey, email_addrs
                  USING PARAMETERS skip_partitioning=true)
            AS (position, orderprices, custkey, email_addrs)
   FROM orders WHERE custkey='342845' ORDER BY orderprices;
 position | orderprices | custkey |         email_addrs
----------+-------------+---------+------------------------------
        2 |             |  342845 | ["br92@cs.example.edu",null]
        3 |       12.00 |  342845 | ["br92@cs.example.edu",null]
        0 |       22.00 |  342845 | ["br92@cs.example.edu",null]
        1 |       35.00 |  342845 | ["br92@cs.example.edu",null]
(4 rows)

The previous example uses the skip_partitioning parameter. Instead of setting it for each call to EXPLODE, you can set it as a session parameter. EXPLODE is part of the ComplexTypesLib UDx library. The following example returns the same results:

=> ALTER SESSION SET UDPARAMETER FOR ComplexTypesLib skip_partitioning=true;

=> SELECT EXPLODE(orderprices, custkey, email_addrs)
            AS (position, orderprices, custkey, email_addrs)
   FROM orders WHERE custkey='342845' ORDER BY orderprices;

You can explode more than one column by specifying the explode_count parameter:

=> SELECT EXPLODE(orderkey, prodkey, orderprices
                  USING PARAMETERS explode_count=2, skip_partitioning=true)
          AS (orderkey,pk_idx,pk_val,ord_idx,ord_val)
   FROM orders
   WHERE orderkey='113-341987';
  orderkey  | pk_idx |  pk_val  | ord_idx | ord_val
------------+--------+----------+---------+---------
 113-341987 |      0 | MG-7190  |       0 |   60.00
 113-341987 |      0 | MG-7190  |       1 |   67.00
 113-341987 |      0 | MG-7190  |       2 |   22.00
 113-341987 |      0 | MG-7190  |       3 |   14.99
 113-341987 |      1 | VA-4028  |       0 |   60.00
 113-341987 |      1 | VA-4028  |       1 |   67.00
 113-341987 |      1 | VA-4028  |       2 |   22.00
 113-341987 |      1 | VA-4028  |       3 |   14.99
 113-341987 |      2 | EH-1247  |       0 |   60.00
 113-341987 |      2 | EH-1247  |       1 |   67.00
 113-341987 |      2 | EH-1247  |       2 |   22.00
 113-341987 |      2 | EH-1247  |       3 |   14.99
 113-341987 |      3 | MS-7018  |       0 |   60.00
 113-341987 |      3 | MS-7018  |       1 |   67.00
 113-341987 |      3 | MS-7018  |       2 |   22.00
 113-341987 |      3 | MS-7018  |       3 |   14.99
(16 rows)

The following example uses a multi-dimensional array:

=> SELECT name, pingtimes FROM network_tests;
 name |                       pingtimes
------+-------------------------------------------------------
 eng1 | [[24.24,25.27,27.16,24.97],[23.97,25.01,28.12,29.5]]
 eng2 | [[27.12,27.91,28.11,26.95],[29.01,28.99,30.11,31.56]]
 qa1  | [[23.15,25.11,24.63,23.91],[22.85,22.86,23.91,31.52]]
(3 rows)

=> SELECT EXPLODE(name, pingtimes USING PARAMETERS explode_count=1) OVER()
   FROM network_tests;
 name | position |           value
------+----------+---------------------------
 eng1 |        0 | [24.24,25.27,27.16,24.97]
 eng1 |        1 | [23.97,25.01,28.12,29.5]
 eng2 |        0 | [27.12,27.91,28.11,26.95]
 eng2 |        1 | [29.01,28.99,30.11,31.56]
 qa1  |        0 | [23.15,25.11,24.63,23.91]
 qa1  |        1 | [22.85,22.86,23.91,31.52]
(6 rows)

You can rewrite the previous query as follows to produce the same results:

=> SELECT name, EXPLODE(pingtimes USING PARAMETERS skip_partitioning=true)
   FROM network_tests;

1.13 - FILTER

Takes an input array and returns an array containing only elements that meet a specified condition.

Takes an input array and returns an array containing only elements that meet a specified condition. This function uses null-safe equality checks when testing elements.

Behavior type

Immutable

Syntax

FILTER(array, lambda-expression )

Arguments

array: Input array.
lambda-expression: Lambda function to apply to each element. The function must return a Boolean value. The first argument to the function is the element, and the optional second element is the index of the element.

Examples

Given a table that contains names and arrays of email addresses, the following query filters out fake email addresses and returns the rest:

=> SELECT name, FILTER(email, e -> NOT REGEXP_LIKE(e,'example.com','i')) AS 'real_email'
   FROM people;
      name      |                   real_email
----------------+-------------------------------------------------
 Elaine Jackson | ["ejackson@somewhere.org","elaine@jackson.com"]
 Frank Adams    | []
 Lee Jones      | ["lee.jones@somewhere.org"]
 M Smith        | ["ms@msmith.com"]
(4 rows)

You can use the results in a WHERE clause to exclude rows that no longer contain any email addresses:

=> SELECT name, FILTER(email, e -> NOT REGEXP_LIKE(e,'example.com','i')) AS 'real_email'
   FROM people
   WHERE ARRAY_LENGTH(real_email) > 0;
      name      |                   real_email
----------------+-------------------------------------------------
 Elaine Jackson | ["ejackson@somewhere.org","elaine@jackson.com"]
 Lee Jones      | ["lee.jones@somewhere.org"]
 M Smith        | ["ms@msmith.com"]
(3 rows)

1.14 - IMPLODE

Takes a column of any scalar type and returns an unbounded array.

Takes a column of any scalar type and returns an unbounded array. Combined with GROUP BY, this function can be used to reverse an EXPLODE operation.

Behavior type

Immutable if the WITHIN GROUP ORDER BY clause specifies a column or set of columns that resolves to unique element values within each output array group.
Volatile otherwise because results are non-commutative.

Syntax

IMPLODE (input-column [ USING PARAMETERS param=value[,...] ] )
    [ within-group-order-by-clause ]

Arguments

input-column

Column of any scalar type from which to create the array.

[within-group-order-by-clause](/en/sql-reference/functions/aggregate-functions/within-group-order-by-clause/)

Sorts elements within each output array group:

WITHIN GROUP (ORDER BY { column-expression[ sort-qualifiers ] }[,...])

sort-qualifiers: { ASC | DESC [ NULLS { FIRST | LAST | AUTO } ] }

Tip

WITHIN GROUP ORDER BY can consume a large amount of memory per group. To minimize memory consumption, create projections that support GROUPBY PIPELINED.

Parameters

allow_truncate: Boolean, if true truncates results when output length exceeds column size. If false (the default), the function returns an error if the output array is too large.
Even if this parameter is set to true, IMPLODE returns an error if any single array element is too large. Truncation removes elements from the output array but does not alter individual elements.
max_binary_size: The maximum binary size in bytes for the returned array. If you omit this parameter, IMPLODE uses the value of the configuration parameter DefaultArrayBinarySize.

Examples

Consider a table with the following contents:

=> SELECT * FROM filtered;

 position | itemprice | itemkey
----------+-----------+---------
        0 |     14.99 |     345
        0 |     27.99 |     567
        1 |     18.99 |     567
        1 |     35.99 |     345
        2 |     14.99 |     123
(5 rows)

The following query calls IMPLODE to assemble prices into arrays (grouped by keys):

=> SELECT itemkey AS key, IMPLODE(itemprice) AS prices
    FROM filtered GROUP BY itemkey ORDER BY itemkey;
 key |      prices
-----+-------------------
 123 | ["14.99"]
 345 | ["35.99","14.99"]
 567 | ["27.99","18.99"]
(3 rows)

You can modify this query by including a WITHIN GROUP ORDER BY clause, which specifies how to sort array elements within each group:

=> SELECT itemkey AS key, IMPLODE(itemprice) WITHIN GROUP (ORDER BY itemprice) AS prices
    FROM filtered GROUP BY itemkey ORDER BY itemkey;
 key |      prices
-----+-------------------
 123 | ["14.99"]
 345 | ["14.99","35.99"]
 567 | ["18.99","27.99"]
(3 rows)

See Arrays and sets (collections) for a fuller example.

1.15 - SET_UNION

Returns a SET containing all elements of two input sets.

If the inputs are both bounded, the bound for the result is the sum of the bounds of the inputs.

If any input is unbounded, the result is unbounded with a binary size that is the sum of the sizes of the inputs.

Behavior type

Immutable

Syntax

SET_UNION(set1,set2)

Arguments

set1, set2: Sets of matching element type

Null-handling

Null arguments are ignored. If one of the inputs is null, the function returns the non-null input. In other words, an argument of NULL is equivalent to SET[].
If both inputs are null, the function returns null.

Examples

=> SELECT SET_UNION(SET[1,2,4], SET[2,3,4,5.9]);
set_union
-----------------------
["1.0","2.0","3.0","4.0","5.9"]
(1 row)

1.16 - STRING_TO_ARRAY

Splits a string containing array values and returns a native one-dimensional array.

Splits a string containing array values and returns a native one-dimensional array. The output does not include the "ARRAY" keyword. This function does not support nested (multi-dimensional) arrays.

This function returns array elements as strings by default. You can cast to other types, as in the following example:

=> SELECT STRING_TO_ARRAY('[1,2,3]')::ARRAY[INT];

Behavior

Immutable

Syntax

STRING_TO_ARRAY(string [USING PARAMETERS param=value[,...]])

The following syntax is deprecated:

STRING_TO_ARRAY(string, delimiter)

Arguments

string: String representation of a one-dimensional array; can be a VARCHAR or LONG VARCHAR column, a literal string, or the string output of an expression.
Spaces in the string are removed unless elements are individually quoted. For example, ' a,b,c' is equivalent to 'a,b,c'. To preserve the space, use '" a","b","c"'.

Parameters

These parameters behave the same way as the corresponding options when loading delimited data (see DELIMITED).

No parameter may have the same value as any other parameter.

collection_delimiter: The character or character sequence used to separate array elements (VARCHAR(8)). You can use any ASCII values in the range E'\000' to E'\177', inclusive.
Default: Comma (',').
collection_open, collection_close: The characters that mark the beginning and end of the array (VARCHAR(8)). It is an error to use these characters elsewhere within the list of elements without escaping them. These characters can be omitted from the input string.
Default: Square brackets ('[' and ']').
collection_null_element: The string representing a null element value (VARCHAR(65000)). You can specify a null value using any ASCII values in the range E'\001' to E'\177' inclusive (any ASCII value except NULL: E'\000').
Default: 'null'
collection_enclose: An optional quote character within which to enclose individual elements, allowing delimiter characters to be embedded in string values. You can choose any ASCII value in the range E'\001' to E'\177' inclusive (any ASCII character except NULL: E'\000'). Elements do not need to be enclosed by this value.
Default: double quote ('"')

Examples

The function uses comma as the default delimiter. You can specify a different value:

=> SELECT STRING_TO_ARRAY('[1,3,5]');
 STRING_TO_ARRAY
-----------------
 ["1","3","5"]
(1 row)

=> SELECT STRING_TO_ARRAY('[t|t|f|t]' USING PARAMETERS collection_delimiter = '|');
  STRING_TO_ARRAY
-------------------
 ["t","t","f","t"]
(1 row)

The bounding brackets are optional:

=> SELECT STRING_TO_ARRAY('t|t|f|t' USING PARAMETERS collection_delimiter = '|');
  STRING_TO_ARRAY
-------------------
 ["t","t","f","t"]
(1 row)

The input can use other characters for open and close:

=> SELECT STRING_TO_ARRAY('{NASA-1683,NASA-7867,SPX-76}' USING PARAMETERS collection_open = '{', collection_close = '}');
          STRING_TO_ARRAY
------------------------------------
 ["NASA-1683","NASA-7867","SPX-76"]
(1 row)

By default the string 'null' in input is treated as a null value:

=> SELECT STRING_TO_ARRAY('{"us-1672",null,"darpa-1963"}' USING PARAMETERS collection_open = '{', collection_close = '}');
        STRING_TO_ARRAY
-------------------------------
 ["us-1672",null,"darpa-1963"]
(1 row)

In the following example, the input comes from a column:

=> SELECT STRING_TO_ARRAY(name USING PARAMETERS collection_delimiter=' ') FROM employees;
    STRING_TO_ARRAY
-----------------------
 ["Howard","Wolowitz"]
 ["Sheldon","Cooper"]
(2 rows)

1.17 - TO_JSON

Returns the JSON representation of a complex-type argument, including mixed and nested complex types.

Returns the JSON representation of a complex-type argument, including mixed and nested complex types. This is the same format that queries of complex-type columns return.

Behavior

Immutable

Syntax

TO_JSON(value)

Arguments

value: Column or literal of a complex type

Examples

These examples query the following table:

=> SELECT name, contact FROM customers;
        name        |                                                        contact
--------------------+-----------------------------------------------------------------------------------------------------------------------
Missy Cooper       | {"street":"911 San Marcos St","city":"Austin","zipcode":73344,"email":["missy@mit.edu","mcooper@cern.gov"]}
Sheldon Cooper     | {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001,"email":["shelly@meemaw.name","cooper@caltech.edu"]}
Leonard Hofstadter | {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001,"email":["hofstadter@caltech.edu"]}
Leslie Winkle      | {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001,"email":[]}
Raj Koothrappali   | {"street":null,"city":"Pasadena","zipcode":91001,"email":["raj@available.com"]}
Stuart Bloom       |
(6 rows)

You can call TO_JSON on a column or on specific fields or array elements:

=> SELECT TO_JSON(contact) FROM customers;
    to_json
-----------------------------------------------------------------------------------------------------------------------
{"street":"911 San Marcos St","city":"Austin","zipcode":73344,"email":["missy@mit.edu","mcooper@cern.gov"]}
{"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001,"email":["shelly@meemaw.name","cooper@caltech.edu"]}
{"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001,"email":["hofstadter@caltech.edu"]}
{"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001,"email":[]}
{"street":null,"city":"Pasadena","zipcode":91001,"email":["raj@available.com"]}

(6 rows)

=> SELECT TO_JSON(contact.email) FROM customers;
    to_json
---------------------------------------------
["missy@mit.edu","mcooper@cern.gov"]
["shelly@meemaw.name","cooper@caltech.edu"]
["hofstadter@caltech.edu"]
[]
["raj@available.com"]

(6 rows)

When calling TO_JSON with a SET, note that duplicates are removed and elements can be reordered:

=> SELECT TO_JSON(SET[1683,7867,76,76]);
    TO_JSON
----------------
[76,1683,7867]
(1 row)

1.18 - UNNEST

Expands the elements of one or more collection columns (ARRAY or SET) into individual rows.

Expands the elements of one or more collection columns (ARRAY or SET) into individual rows. UNNEST is similar to EXPLODE, but UNNEST returns only the elements, while EXPLODE returns elements and their positions.

If called with a single array, UNNEST returns the elements in a column named value. If called with two or more arrays, it returns columns named val_column-name. You can use an AS clause in the SELECT to change these names.

By default, UNNEST does not partition its input and ignores an OVER() clause if present.

Behavior type

Immutable

Syntax

UNNEST (column[,...])
 [USING PARAMETERS param=value])
[ OVER ( [window-partition-clause

Arguments

column: Collection column in the table being queried.
OVER(...): How to partition and sort input data. The input data is the result set that the query returns after it evaluates FROM, WHERE, GROUP BY, and HAVING clauses.
This clause only applies if skip_partitioning is false.

Parameters

skip_partitioning: Whether to skip partitioning and ignore the OVER clause if present. UNNEST translates a single row of input into multiple rows of output, one per collection element. There is, therefore, usually no benefit to partitioning the input first. Skipping partitioning can help a query avoid an expensive sort or merge operation.
Default: true

Null-handling

This function expands each element in a collection into a row, including null elements. If the input column is NULL or an empty collection, the function produces no rows for that column:

=> SELECT UNNEST(ARRAY[1,2,null,4]) OVER();
 value
-------
     1
     2

     4
(4 rows)

=> SELECT UNNEST(ARRAY[]::ARRAY[INT]) OVER();
 value
-------
(0 rows)

=> SELECT UNNEST(NULL::ARRAY[INT]) OVER();
 value
-------
(0 rows)

Joining on results

You can use the output of this function as if it were a relation by using CROSS JOIN or LEFT JOIN LATERAL in a query. Other JOIN types are not supported.

Consider the following table of students and exam scores:

=> SELECT * FROM tests;
 student |    scores     |    questions
---------+---------------+-----------------
 Bob     | [92,78,79]    | [20,20,100]
 Lee     |               |
 Pat     | []            | []
 Sam     | [97,98,85]    | [20,20,100]
 Tom     | [68,75,82,91] | [20,20,100,100]
(5 rows)

The following query finds the best test scores across all students who have scores:

=> SELECT student, score FROM tests
CROSS JOIN UNNEST(scores) AS t (score)
ORDER BY score DESC;
 student | score
---------+-------
 Sam     |    98
 Sam     |    97
 Bob     |    92
 Tom     |    91
 Sam     |    85
 Tom     |    82
 Bob     |    79
 Bob     |    78
 Tom     |    75
 Tom     |    68
(10 rows)

The following query returns maximum and average per-question scores, considering both the exam score and the number of questions:

=> SELECT student, MAX(score/qcount), AVG(score/qcount) FROM tests
CROSS JOIN UNNEST(scores, questions) AS t(score, qcount)
GROUP BY student;
 student |         MAX          |       AVG
---------+----------------------+------------------
 Bob     | 4.600000000000000000 | 3.04333333333333
 Sam     | 4.900000000000000000 | 3.42222222222222
 Tom     | 4.550000000000000000 |             2.37
(3 rows)

These queries produce results for three of the five students. One student has a null value for scores and another has an empty array. These rows are not included in the function's output.

To include null and empty arrays in output, use LEFT JOIN LATERAL instead of CROSS JOIN:

=> SELECT student, MIN(score), AVG(score) FROM tests
LEFT JOIN LATERAL UNNEST(scores) AS t (score)
GROUP BY student;
 student | MIN |       AVG
---------+-----+------------------
 Bob     |  78 |               83
 Lee     |     |
 Pat     |     |
 Sam     |  85 | 93.3333333333333
 Tom     |  68 |               79
(5 rows)

The LATERAL keyword is required with LEFT JOIN. It is optional for CROSS JOIN.

Examples

Consider a table with the following definition:

=> CREATE TABLE orders (
        orderkey VARCHAR, custkey INT,
        prodkey ARRAY[VARCHAR], orderprices ARRAY[DECIMAL(12,2)],
        email_addrs ARRAY[VARCHAR]);

The following query expands one of the array columns. One of the elements is null:

=> SELECT UNNEST(orderprices) AS price, custkey, email_addrs
   FROM orders WHERE custkey='342845' ORDER BY price;
 price | custkey |       email_addrs
-------+---------+-------------------------
       |  342845 | ["br92@cs.example.edu"]
 12.00 |  342845 | ["br92@cs.example.edu"]
 22.00 |  342845 | ["br92@cs.example.edu"]
 35.00 |  342845 | ["br92@cs.example.edu"]
(4 rows)

UNNEST can expand more than one column:

=> SELECT orderkey, UNNEST(prodkey, orderprices)
   FROM orders WHERE orderkey='113-341987';
  orderkey  | val_prodkey | val_orderprices
------------+-------------+-----------------
 113-341987 | MG-7190     |           60.00
 113-341987 | MG-7190     |           67.00
 113-341987 | MG-7190     |           22.00
 113-341987 | MG-7190     |           14.99
 113-341987 | VA-4028     |           60.00
 113-341987 | VA-4028     |           67.00
 113-341987 | VA-4028     |           22.00
 113-341987 | VA-4028     |           14.99
 113-341987 | EH-1247     |           60.00
 113-341987 | EH-1247     |           67.00
 113-341987 | EH-1247     |           22.00
 113-341987 | EH-1247     |           14.99
 113-341987 | MS-7018     |           60.00
 113-341987 | MS-7018     |           67.00
 113-341987 | MS-7018     |           22.00
 113-341987 | MS-7018     |           14.99
(16 rows)

2 - Date/time functions

Date and time functions perform conversion, extraction, or manipulation operations on date and time data types and can return date and time information.

Usage

Functions that take TIME or TIMESTAMP inputs come in two variants:

TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE
TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE

For brevity, these variants are not shown separately.

The + and * operators come in commutative pairs; for example, both DATE + INTEGER and INTEGER + DATE. We show only one of each such pair.

Daylight savings time considerations

When adding an INTERVAL value to (or subtracting an INTERVAL value from) a TIMESTAMP WITH TIME ZONE value, the days component advances (or decrements) the date of the TIMESTAMP WITH TIME ZONE by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means INTERVAL '1 day' does not necessarily equal INTERVAL '24 hours'.

For example, with the session time zone set to CST7CDT:

TIMESTAMP WITH TIME ZONE '2014-04-02 12:00-07' + INTERVAL '1 day'

produces

TIMESTAMP WITH TIME ZONE '2014-04-03 12:00-06'

Adding INTERVAL '24 hours' to the same initial TIMESTAMP WITH TIME ZONE produces

TIMESTAMP WITH TIME ZONE '2014-04-03 13:00-06',

This result occurs because there is a change in daylight saving time at 2014-04-03 02:00 in time zone CST7CDT.

Date/time functions in transactions

Certain date/time functions such as CURRENT_TIMESTAMP and NOW return the start time of the current transaction; for the duration of that transaction, they return the same value. Other date/time functions such as TIMEOFDAY always return the current time.

2.1 - ADD_MONTHS

Adds the specified number of months to a date and returns the sum as a DATE.

Adds the specified number of months to a date and returns the sum as a DATE. In general, ADD_MONTHS returns a date with the same day component as the start date. For example:

=> SELECT ADD_MONTHS ('2015-09-15'::date, -2) "2 Months Ago";
 2 Months Ago
--------------
 2015-07-15
(1 row)

Two exceptions apply:

If the start date's day component is greater than the last day of the result month, ADD_MONTHS returns the last day of the result month. For example:
```
=> SELECT ADD_MONTHS ('31-Jan-2016'::TIMESTAMP, 1) "Leap Month";
 Leap Month
------------
 2016-02-29
(1 row)
```
If the start date's day component is the last day of that month, and the result month has more days than the start date month, ADD_MONTHS returns the last day of the result month. For example:
```
=> SELECT ADD_MONTHS ('2015-09-30'::date,-1) "1 Month Ago";
 1 Month Ago
-------------
 2015-08-31
(1 row)
```

Behavior type

Immutable if the start-date argument is a TIMESTAMP or DATE
Stable if the start-date argument is a TIMESTAMPTZ

Syntax

ADD_MONTHS ( start-date, num-months );

Parameters

start-date

The date to process, an expression that evaluates to one of the following data types:

DATE
TIMESTAMP
TIMESTAMPTZ

num-months

An integer expression that specifies the number of months to add to or subtract from start-date.

Examples

Add one month to the current date:

=> SELECT CURRENT_DATE Today;
   Today
------------
 2016-05-05
(1 row)

VMart=> SELECT ADD_MONTHS(CURRENT_TIMESTAMP,1);
 ADD_MONTHS
------------
 2016-06-05
(1 row)

Subtract four months from the current date:

=> SELECT ADD_MONTHS(CURRENT_TIMESTAMP, -4);
 ADD_MONTHS
------------
 2016-01-05
(1 row)

Add one month to January 31 2016:

=> SELECT ADD_MONTHS('31-Jan-2016'::TIMESTAMP, 1) "Leap Month";
 Leap Month
------------
 2016-02-29
(1 row)

The following example sets the timezone to EST; it then adds 24 months to a TIMESTAMPTZ that specifies a PST time zone, so ADD_MONTHS takes into account the time change:

=> SET TIME ZONE 'America/New_York';
SET
VMart=> SELECT ADD_MONTHS('2008-02-29 23:30 PST'::TIMESTAMPTZ, 24);
 ADD_MONTHS
------------
 2010-03-01
(1 row)

2.2 - AGE_IN_MONTHS

Returns the difference in months between two dates, expressed as an integer.

Behavior type

Immutable if both date arguments are of data type TIMESTAMP
Stable if either date is a TIMESTAMPTZ or only one argument is supplied

Syntax

AGE_IN_MONTHS ( [ date1,] date2 )

Parameters

date1 date2

Specify the boundaries of the period to measure. If you supply only one argument, Vertica sets date2 to the current date. Both parameters must evaluate to one of the following data types:

DATE
TIMESTAMP
TIMESTAMPTZ

If date1 < date2, AGE_IN_MONTHS returns a negative value.

Examples

Get the age in months of someone born March 2 1972, as of June 21 1990:

=> SELECT AGE_IN_MONTHS('1990-06-21'::TIMESTAMP, '1972-03-02'::TIMESTAMP);
  AGE_IN_MONTHS
---------------
           219
(1 row)

If the first date is less than the second date, AGE_IN_MONTHS returns a negative value

=> SELECT AGE_IN_MONTHS('1972-03-02'::TIMESTAMP, '1990-06-21'::TIMESTAMP);
AGE_IN_MONTHS
---------------
-220
(1 row)

Get the age in months of someone who was born November 21 1939, as of today:

=> SELECT AGE_IN_MONTHS ('1939-11-21'::DATE);
 AGE_IN_MONTHS
---------------
           930
(1 row)

2.3 - AGE_IN_YEARS

Returns the difference in years between two dates, expressed as an integer.

Behavior type

Immutable if both date arguments are of data type TIMESTAMP
Stable if either date is a TIMESTAMPTZ or only one argument is supplied

Syntax

AGE_IN_YEARS( [ date1,] date2 )

Parameters

date1 date2

Specify the boundaries of the period to measure. If you supply only one argument, Vertica sets date1 to the current date. Both parameters must evaluate to one of the following data types:

DATE
TIMESTAMP
TIMESTAMPTZ

If date1 < date2, AGE_IN_YEARS returns a negative value.

Examples

Get the age of someone born March 2 1972, as of June 21 1990:

=> SELECT AGE_IN_YEARS('1990-06-21'::TIMESTAMP, '1972-03-02'::TIMESTAMP);
 AGE_IN_YEARS
--------------
           18
(1 row)

If the first date is earlier than the second date, AGE_IN_YEARS returns a negative number:

=> SELECT AGE_IN_YEARS('1972-03-02'::TIMESTAMP, '1990-06-21'::TIMESTAMP);
AGE_IN_YEARS
--------------
          -19
(1 row)

Get the age of someone who was born November 21 1939, as of today:

=> SELECT AGE_IN_YEARS('1939-11-21'::DATE);
 AGE_IN_YEARS
--------------
           77
(1 row)

2.4 - CLOCK_TIMESTAMP

Returns a value of type TIMESTAMP WITH TIMEZONE that represents the current system-clock time.

CLOCK_TIMESTAMP uses the date and time supplied by the operating system on the server to which you are connected, which should be the same across all servers. The value changes each time you call it.

Behavior type

Volatile

Syntax

CLOCK_TIMESTAMP()

Examples

The following command returns the current time on your system:

SELECT CLOCK_TIMESTAMP() "Current Time";
         Current Time
------------------------------
 2010-09-23 11:41:23.33772-04
(1 row)

Each time you call the function, you get a different result. The difference in this example is in microseconds:

SELECT CLOCK_TIMESTAMP() "Time 1", CLOCK_TIMESTAMP() "Time 2";
            Time 1             |            Time 2
-------------------------------+-------------------------------
 2010-09-23 11:41:55.369201-04 | 2010-09-23 11:41:55.369202-04
(1 row)

2.5 - CURRENT_DATE

Returns the date (date-type value) on which the current transaction started.

Behavior type

Stable

Syntax

CURRENT_DATE()

Note

You can call this function without parentheses.

Examples

SELECT CURRENT_DATE;
  ?column?
------------
 2010-09-23
(1 row)

2.6 - CURRENT_TIME

Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.

Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.

The return value does not change during the transaction. Thus, multiple calls to CURRENT_TIME within the same transaction return the same timestamp.

Behavior type

Stable

Syntax

CURRENT_TIME  [ ( precision ) ]

Note

If you specify a column label without precision, you must also omit parentheses.

Parameters

precision: An integer value between 0-6, specifies to round the seconds fraction field result to the specified number of digits.

Examples


=> SELECT CURRENT_TIME(1) AS Time;
     Time
---------------
 06:51:45.2-07
(1 row)
=> SELECT CURRENT_TIME(5) AS Time;
       Time
-------------------
 06:51:45.18435-07
(1 row)

2.7 - CURRENT_TIMESTAMP

Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.

Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.

The return value does not change during the transaction. Thus, multiple calls to CURRENT_TIMESTAMP within the same transaction return the same timestamp.

Behavior type

Stable

Syntax

CURRENT_TIMESTAMP ( precision )

Parameters

precision: An integer value between 0-6, specifies to round the seconds fraction field result to the specified number of digits.

Examples


=> SELECT CURRENT_TIMESTAMP(1) AS time;
           time
--------------------------
 2017-03-27 06:50:49.7-07
(1 row)
=> SELECT CURRENT_TIMESTAMP(5) AS time;
             time
------------------------------
 2017-03-27 06:50:49.69967-07
(1 row)

2.8 - DATE

Converts the input value to a DATE data type.

Converts the input value to a DATE data type.

Behavior type

Immutable if the input value is a TIMESTAMP, DATE, VARCHAR, or integer
Stable if the input value is a TIMESTAMPTZ

Syntax

DATE ( value )

Parameters

value

The value to convert, one of the following:

TIMESTAMP, TIMESTAMPTZ, VARCHAR, or another DATE.
Integer: Vertica treats the integer as the number of days since 01/01/0001 and returns the date.

Examples

=> SELECT DATE (1);
    DATE
------------
 0001-01-01
(1 row)

=> SELECT DATE (734260);
    DATE
------------
 2011-05-03
(1 row)

=> SELECT DATE('TODAY');
    DATE
------------
 2016-12-07
(1 row)

2.9 - DATE_PART

Extracts a sub-field such as year or hour from a date/time expression, equivalent to the the SQL-standard function EXTRACT.

Extracts a sub-field such as year or hour from a date/time expression, equivalent to the the SQL-standard function EXTRACT.

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or INTERVAL
Stable if the specified date is a TIMESTAMPTZ

Syntax

DATE_PART ( 'field', date )

Parameters

field

A constant value that specifies the sub-field to extract from date (see Field Values below).

date

The date to process, an expression that evaluates to one of the following data types:

DATE (cast to TIMESTAMP)
TIMESTAMP/TIMESTAMPTZ
INTERVAL

Field values

CENTURY

The century number.

The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from –1 to 1.

DAY

The day (of the month) field (1–31).

DECADE

The year field divided by 10.

DOQ

The day within the current quarter. DOQ recognizes leap year days.

DOW

Zero-based day of the week, where Sunday=0.

Note

EXTRACT's day of week numbering differs from the function TO_CHAR .

DOY

The day of the year (1–365/366)

EPOCH

Specifies to return one of the following:

For DATE and TIMESTAMP values: the number of seconds before or since 1970-01-01 00:00:00-00 (if before, a negative number).
For INTERVAL values, the total number of seconds in the interval.

HOUR

The hour field (0–23).

ISODOW

The ISO day of the week, an integer between 1 and 7 where Monday is 1.

ISOWEEK

The ISO week of the year, an integer between 1 and 53.

ISOYEAR

The ISO year.

MICROSECONDS

The seconds field, including fractional parts, multiplied by 1,000,000. This includes full seconds.

MILLENNIUM

The millennium number, where the first millennium is 1 and each millenium starts on 01-01-y001. For example, millennium 2 starts on 01-01-1001.

MILLISECONDS

The seconds field, including fractional parts, multiplied by 1000. This includes full seconds.

MINUTE

The minutes field (0 - 59).

MONTH

For TIMESTAMP values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11).

QUARTER

The calendar quarter of the specified date as an integer, where the January-March quarter is 1, valid only for TIMESTAMP values.

SECOND

The seconds field, including fractional parts, 0–59, or 0-60 if the operating system implements leap seconds.

TIME ZONE

The time zone offset from UTC, in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.

TIMEZONE_HOUR

The hour component of the time zone offset.

TIMEZONE_MINUTE

The minute component of the time zone offset.

WEEK

The number of the week of the calendar year that the day is in.

YEAR

The year field. There is no 0 AD, so subtract BC years from AD years accordingly.

Notes

According to the ISO-8601 standard, the week starts on Monday, and the first week of a year contains January 4. Thus, an early January date can sometimes be in the week 52 or 53 of the previous calendar year. For example:

=> SELECT YEAR_ISO('01-01-2016'::DATE), WEEK_ISO('01-01-2016'), DAYOFWEEK_ISO('01-01-2016');
 YEAR_ISO | WEEK_ISO | DAYOFWEEK_ISO
----------+----------+---------------
     2015 |       53 |             5
(1 row)

Examples

Extract the day value:

SELECT DATE_PART('DAY', TIMESTAMP '2009-02-24 20:38:40') "Day";
  Day
-----
  24
(1 row)

Extract the month value:

SELECT DATE_PART('MONTH', '2009-02-24 20:38:40'::TIMESTAMP) "Month";
  Month
-------
     2
(1 row)

Extract the year value:

SELECT DATE_PART('YEAR', '2009-02-24 20:38:40'::TIMESTAMP) "Year";
  Year
------
 2009
(1 row)

Extract the hours:

SELECT DATE_PART('HOUR', '2009-02-24 20:38:40'::TIMESTAMP) "Hour";
  Hour
------
   20
(1 row)

Extract the minutes:

SELECT DATE_PART('MINUTES', '2009-02-24 20:38:40'::TIMESTAMP) "Minutes";
  Minutes
---------
      38
(1 row)

Extract the day of quarter (DOQ):

SELECT DATE_PART('DOQ', '2009-02-24 20:38:40'::TIMESTAMP) "DOQ";
 DOQ
-----
  55
(1 row)

2.10 - DATE_TRUNC

Truncates date and time values to the specified precision.

Truncates date and time values to the specified precision. The return value is the same data type as the input value. All fields that are less than the specified precision are set to 0, or to 1 for day and month.

Behavior type

Stable

Syntax

DATE_TRUNC( precision, trunc-target )

Parameters

precision: A string constant that specifies precision for the truncated value. See Precision Field Values below. The precision must be valid for the trunc-target date or time.
trunc-target: Valid date/time expression.

Precision field values

MILLENNIUM

The millennium number.

CENTURY

The century number.

The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries.

DECADE

The year field divided by 10.

YEAR

The year field. Keep in mind there is no 0 AD, so subtract BC years from AD years with care.

QUARTER

The calendar quarter of the specified date as an integer, where the January-March quarter is 1.

MONTH

For timestamp values, the number of the month within the year (1–12) ; for interval values the number of months, modulo 12 (0–11).

WEEK

The number of the week of the year that the day is in.

=> SELECT YEAR_ISO('01-01-2016'::DATE), WEEK_ISO('01-01-2016'), DAYOFWEEK_ISO('01-01-2016');
 YEAR_ISO | WEEK_ISO | DAYOFWEEK_ISO
----------+----------+---------------
     2015 |       53 |             5
(1 row)

DAY

The day (of the month) field (1–31).

HOUR

The hour field (0–23).

MINUTE

The minutes field (0–59).

SECOND

The seconds field, including fractional parts (0–59) (60 if leap seconds are implemented by the operating system).

MILLISECONDS

The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

MICROSECONDS

The seconds field, including fractional parts, multiplied by 1,000,000. This includes full seconds.

Examples

The following example sets the field value as hour and returns the hour, truncating the minutes and seconds:

=> SELECT DATE_TRUNC('HOUR', TIMESTAMP '2012-02-24 13:38:40') AS HOUR;
        HOUR
---------------------
 2012-02-24 13:00:00
(1 row)

The following example returns the year from the input timestamptz '2012-02-24 13:38:40'. The function also defaults the month and day to January 1, truncates the hour:minute:second of the timestamp, and appends the time zone (-05):

=> SELECT DATE_TRUNC('YEAR', TIMESTAMPTZ '2012-02-24 13:38:40') AS YEAR;
          YEAR
------------------------
 2012-01-01 00:00:00-05
(1 row)

The following example returns the year and month and defaults day of month to 1, truncating the rest of the string:

=> SELECT DATE_TRUNC('MONTH', TIMESTAMP '2012-02-24 13:38:40') AS MONTH;
        MONTH
---------------------
 2012-02-01 00:00:00
(1 row)

2.11 - DATEDIFF

Returns the time span between two dates, in the intervals specified.

Returns the time span between two dates, in the intervals specified. DATEDIFF excludes the start date in its calculation.

Behavior type

Immutable if start and end dates are TIMESTAMP , DATE, TIME, or INTERVAL
Stable if start and end dates are TIMESTAMPTZ

Syntax

DATEDIFF ( datepart, start, end );

Parameters

datepart

Specifies the type of date or time intervals that DATEDIFF returns. If datepart is an expression, it must be enclosed in parentheses:

DATEDIFF((expression), start, end);

datepart must evaluate to one of the following string literals, either quoted or unquoted:

year | yy | yyyy
quarter | qq | q
month | mm | m
day | dayofyear | dd | d | dy | y
week | wk | ww
hour | hh
minute | mi | n
second | ss | s
millisecond | ms
microsecond | mcs | us

start, end

Specify the start and end dates, where start and end evaluate to one of the following data types:

If end < start, DATEDIFF returns a negative value.

Note

TIME and INTERVAL data types are invalid for start and end dates if datepart is set to year, quarter, or month.

Compatible start and end date data types

The following table shows which data types can be matched as start and end dates:

	`DATE`	`TIMESTAMP`	`TIMESTAMPTZ`	`TIME`	`INTERVAL`
`DATE`	•	•	•
`TIMESTAMP`	•	•	•
`TIMESTAMPTZ`	•	•	•
`TIME`				•
`INTERVAL`					•

For example, if you set the start date to an INTERVAL data type, the end date must also be an INTERVAL, otherwise Vertica returns an error:

 SELECT DATEDIFF(day, INTERVAL '26 days', INTERVAL '1 month ');
 datediff
----------
        4
(1 row)

Date part intervals

DATEDIFF uses the datepart argument to calculate the number of intervals between two dates, rather than the actual amount of time between them. DATEDIFF uses the following cutoff points to calculate those intervals:

year: January 1
quarter: January 1, April 1, July 1, October 1
month: the first day of the month
week: Sunday at midnight (24:00)

For example, if datepart is set to year, DATEDIFF uses January 01 to calculate the number of years between two dates. The following DATEDIFF statement sets datepart to year, and specifies a time span 01/01/2005 - 06/15/2008:

SELECT DATEDIFF(year, '01-01-2005'::date, '12-31-2008'::date);
 datediff
----------
        3
(1 row)

DATEDIFF always excludes the start date when it calculates intervals—in this case, 01/01//2005. DATEDIFF considers only calendar year starts in its calculation, so in this case it only counts years 2006, 2007, and 2008. The function returns 3, although the actual time span is nearly four years.

If you change the start and end dates to 12/31/2004 and 01/01/2009, respectively, DATEDIFF also counts years 2005 and 2009. This time, it returns 5, although the actual time span is just over four years:

=> SELECT DATEDIFF(year, '12-31-2004'::date, '01-01-2009'::date);
 datediff
----------
        5
(1 row)

Similarly, DATEDIFF uses month start dates when it calculates the number of months between two dates. Thus, given the following statement, DATEDIFF counts months February through September and returns 8:

=> SELECT DATEDIFF(month, '01-31-2005'::date, '09-30-2005'::date);
 datediff
----------
        8
(1 row)

2.12 - DAY

Returns as an integer the day of the month from the input value.

Behavior type

Immutable if the input value is a TIMESTAMP, DATE, VARCHAR, or INTEGER
Stable if the specified date is a TIMESTAMPTZ

Syntax

DAY ( value )

Parameters

value: The value to convert, one of the following: TIMESTAMP, TIMESTAMPTZ, INTERVAL, VARCHAR, or INTEGER.

Examples

=> SELECT DAY (6);
 DAY
-----
   6
(1 row)

=> SELECT DAY(TIMESTAMP 'sep 22, 2011 12:34');
 DAY
-----
  22
(1 row)

=> SELECT DAY('sep 22, 2011 12:34');
 DAY
-----
  22
(1 row)

=> SELECT DAY(INTERVAL '35 12:34');
 DAY
-----
  35
(1 row)

2.13 - DAYOFMONTH

Returns the day of the month as an integer.

Behavior type

Immutable if thetarget date is a TIMESTAMP, DATE, or VARCHAR
Stable if the target date is aTIMESTAMPTZ

Syntax

DAYOFMONTH ( date )

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT DAYOFMONTH (TIMESTAMP 'sep 22, 2011 12:34');
 DAYOFMONTH
------------
         22
(1 row)

2.14 - DAYOFWEEK

Returns the day of the week as an integer, where Sunday is day 1.

Behavior type

Immutable if thetarget date is a TIMESTAMP, DATE, or VARCHAR
Stable if the target date is aTIMESTAMPTZ

Syntax

DAYOFWEEK ( date )

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT DAYOFWEEK (TIMESTAMP 'sep 17, 2011 12:34');
 DAYOFWEEK
-----------
         7
(1 row)

2.15 - DAYOFWEEK_ISO

Returns the ISO 8061 day of the week as an integer, where Monday is day 1.

Behavior type

Immutable if thetarget date is a TIMESTAMP, DATE, or VARCHAR
Stable if the target date is aTIMESTAMPTZ

Syntax

DAYOFWEEK_ISO ( date )

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT DAYOFWEEK_ISO(TIMESTAMP 'Sep 22, 2011 12:34');
 DAYOFWEEK_ISO
---------------
             4
(1 row)

The following example shows how to combine the DAYOFWEEK_ISO, WEEK_ISO, and YEAR_ISO functions to find the ISO day of the week, week, and year:

=> SELECT DAYOFWEEK_ISO('Jan 1, 2000'), WEEK_ISO('Jan 1, 2000'),YEAR_ISO('Jan1,2000');
 DAYOFWEEK_ISO | WEEK_ISO | YEAR_ISO
---------------+----------+----------
             6 |       52 |     1999
(1 row)

2.16 - DAYOFYEAR

Returns the day of the year as an integer, where January 1 is day 1.

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Syntax

DAYOFYEAR ( date )

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT DAYOFYEAR (TIMESTAMP 'SEPT 22,2011 12:34');
 DAYOFYEAR
-----------
       265
(1 row)

2.17 - DAYS

Returns the integer value of the specified date, where 1 AD is 1.

Returns the integer value of the specified date, where 1 AD is 1. If the date precedes 1 AD, DAYS returns a negative integer.

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Syntax

DAYS ( date )

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT DAYS (DATE '2011-01-22');
  DAYS
--------
 734159
(1 row)

=> SELECT DAYS (DATE 'March 15, 0044 BC');
  DAYS
--------
 -15997
(1 row)

2.18 - EXTRACT

Retrieves sub-fields such as year or hour from date/time values and returns values of type NUMERIC.

Retrieves sub-fields such as year or hour from date/time values and returns values of type NUMERIC. EXTRACT is intended for computational processing, rather than for formatting date/time values for display.

Behavior type

Immutable if the specified date is a TIMESTAMP, DATE, or INTERVAL
Stable if the specified date is aTIMESTAMPTZ

Syntax

EXTRACT ( field FROM date )

Parameters

field

A constant value that specifies the sub-field to extract from date (see Field Values below).

date

The date to process, an expression that evaluates to one of the following data types:

DATE (cast to TIMESTAMP)
TIMESTAMP/TIMESTAMPTZ
INTERVAL

Field values

CENTURY

The century number.

The first century starts at 0001-01-01 00:00:00 AD. This definition applies to all Gregorian calendar countries. There is no century number 0, you go from –1 to 1.

DAY

The day (of the month) field (1–31).

DECADE

The year field divided by 10.

DOQ

The day within the current quarter. DOQ recognizes leap year days.

DOW

Zero-based day of the week, where Sunday=0.

Note

EXTRACT's day of week numbering differs from the function TO_CHAR .

DOY

The day of the year (1–365/366)

EPOCH

Specifies to return one of the following:

For DATE and TIMESTAMP values: the number of seconds before or since 1970-01-01 00:00:00-00 (if before, a negative number).
For INTERVAL values, the total number of seconds in the interval.

HOUR

The hour field (0–23).

ISODOW

The ISO day of the week, an integer between 1 and 7 where Monday is 1.

ISOWEEK

The ISO week of the year, an integer between 1 and 53.

ISOYEAR

The ISO year.

MICROSECONDS

The seconds field, including fractional parts, multiplied by 1,000,000. This includes full seconds.

MILLENNIUM

The millennium number, where the first millennium is 1 and each millenium starts on 01-01-y001. For example, millennium 2 starts on 01-01-1001.

MILLISECONDS

The seconds field, including fractional parts, multiplied by 1000. This includes full seconds.

MINUTE

The minutes field (0 - 59).

MONTH

For TIMESTAMP values, the number of the month within the year (1 - 12) ; for interval values the number of months, modulo 12 (0 - 11).

QUARTER

The calendar quarter of the specified date as an integer, where the January-March quarter is 1, valid only for TIMESTAMP values.

SECOND

The seconds field, including fractional parts, 0–59, or 0-60 if the operating system implements leap seconds.

TIME ZONE

The time zone offset from UTC, in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC.

TIMEZONE_HOUR

The hour component of the time zone offset.

TIMEZONE_MINUTE

The minute component of the time zone offset.

WEEK

The number of the week of the calendar year that the day is in.

YEAR

The year field. There is no 0 AD, so subtract BC years from AD years accordingly.

Examples

Extract the day of the week and day in quarter from the current TIMESTAMP:

=> SELECT CURRENT_TIMESTAMP AS NOW;
              NOW
-------------------------------
 2016-05-03 11:36:08.829004-04
(1 row)
=> SELECT EXTRACT (DAY FROM CURRENT_TIMESTAMP);
 date_part
-----------
         3
(1 row)
=> SELECT EXTRACT (DOQ FROM CURRENT_TIMESTAMP);
 date_part
-----------
        33
(1 row)

Extract the timezone hour from the current time:

=> SELECT CURRENT_TIMESTAMP;
           ?column?
-------------------------------
 2016-05-03 11:36:08.829004-04
(1 row)

=>  SELECT EXTRACT(TIMEZONE_HOUR FROM CURRENT_TIMESTAMP);
 date_part
-----------
        -4
(1 row)

Extract the number of seconds since 01-01-1970 00:00:

=> SELECT EXTRACT(EPOCH FROM '2001-02-16 20:38:40-08'::TIMESTAMPTZ);
    date_part
------------------
 982384720.000000
(1 row)

Extract the number of seconds between 01-01-1970 00:00 and 5 days 3 hours before:

=> SELECT EXTRACT(EPOCH FROM -'5 days 3 hours'::INTERVAL);
   date_part
----------------
 -442800.000000
(1 row)

Convert the results from the last example to a TIMESTAMP:

=> SELECT 'EPOCH'::TIMESTAMPTZ -442800  * '1 second'::INTERVAL;
        ?column?
------------------------
 1969-12-26 16:00:00-05
(1 row)

2.19 - GETDATE

Returns the current statement's start date and time as a TIMESTAMP value.

Returns the current statement's start date and time as a TIMESTAMP value. This function is identical to SYSDATE.

GETDATE uses the date and time supplied by the operating system on the server to which you are connected, which is the same across all servers. Internally, GETDATE converts STATEMENT_TIMESTAMP from TIMESTAMPTZ to TIMESTAMP.

Behavior type

Stable

Syntax

GETDATE()

Examples

=> SELECT GETDATE();
          GETDATE
----------------------------
 2011-03-07 13:21:29.497742
(1 row)

2.20 - GETUTCDATE

Returns the current statement's start date and time as a TIMESTAMP value.

Returns the current statement's start date and time as a TIMESTAMP value.

GETUTCDATE uses the date and time supplied by the operating system on the server to which you are connected, which is the same across all servers. Internally, GETUTCDATE converts STATEMENT_TIMESTAMP at TIME ZONE 'UTC'.

Behavior type

Stable

Syntax

GETUTCDATE()

Examples

=> SELECT GETUTCDATE();
         GETUTCDATE
----------------------------
 2011-03-07 20:20:26.193052
(1 row)

2.21 - HOUR

Returns the hour portion of the specified date as an integer, where 0 is 00:00 to 00:59.

Behavior type

Immutable if thespecified date is a TIMESTAMP
Stable if the specified date is aTIMESTAMPTZ

Syntax

HOUR( date )

Parameters

date

The date to process, one of the following data types:

INTERVAL

Examples

=> SELECT HOUR (TIMESTAMP 'sep 22, 2011 12:34');
 HOUR
------
   12
(1 row)
=> SELECT HOUR (INTERVAL '35 12:34');
 HOUR
------
   12
(1 row)
=> SELECT HOUR ('12:34');
 HOUR
------
   12
(1 row)

2.22 - ISFINITE

Tests for the special TIMESTAMP constant INFINITY and returns a value of type BOOLEAN.

Tests for the special TIMESTAMP constant INFINITY and returns a value of type BOOLEAN.

Behavior type

Immutable

Syntax

ISFINITE ( timestamp )

Parameters

timestamp: Expression of type TIMESTAMP

Examples

SELECT ISFINITE(TIMESTAMP '2009-02-16 21:28:30');
 ISFINITE
----------
 t
(1 row)
SELECT ISFINITE(TIMESTAMP 'INFINITY');
 ISFINITE
----------
 f
(1 row)

2.23 - JULIAN_DAY

Returns the integer value of the specified day according to the Julian calendar, where day 1 is the first day of the Julian period, January 1, 4713 BC (on the Gregorian calendar, November 24, 4714 BC).

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Syntax

JULIAN_DAY ( date )

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT JULIAN_DAY (DATE 'MARCH 15, 0044 BC');
 JULIAN_DAY
------------
    1705428
(1 row)

=> SELECT JULIAN_DAY (DATE '2001-01-01');
 JULIAN_DAY
------------
    2451911
(1 row)

2.24 - LAST_DAY

Returns the last day of the month in the specified date.

Behavior type

Immutable if the specified is a TIMESTAMP or DATE
Stable if the specified date is a TIMESTAMPTZ

Syntax

LAST_DAY ( date )

Parameters

date

The date to process, one of the following data types:

Calculating first day of month

SQL does not support any function that returns the first day in the month of a given date. You must use other functions to work around this limitation. For example:

=> SELECT DATE ('2022/07/04') - DAYOFMONTH ('2022/07/04') +1;
  ?column?
------------
 2022-07-01
(1 row)

=> SELECT LAST_DAY('1929/06/06') - (SELECT DAY(LAST_DAY('1929/06/06'))-1);
  ?column?
------------
 1929-06-01
(1 row)

Examples

The following example returns the last day of February as 29 because 2016 is a leap year:

=> SELECT LAST_DAY('2016-02-28 23:30 PST') "Last Day";
  Last Day
------------
 2016-02-29
(1 row)

The following example returns the last day of February in a non-leap year:

> SELECT LAST_DAY('2017/02/03') "Last";
    Last
------------
 2017-02-28
(1 row)

The following example returns the last day of March, after converting the string value to the specified DATE type:

=> SELECT LAST_DAY('2003/03/15') "Last";
    Last
------------
 2012-03-31
(1 row)

2.25 - LOCALTIME

Returns a value of type TIME that represents the start of the current transaction.

Returns a value of type TIME that represents the start of the current transaction.

The return value does not change during the transaction. Thus, multiple calls to LOCALTIME within the same transaction return the same timestamp.

Behavior type

Stable

Syntax

LOCALTIME [ ( precision ) ]

Parameters

precision: Rounds the result to the specified number of fractional digits in the seconds field.

Examples

=> CREATE TABLE t1 (a int, b int);
CREATE TABLE

=> INSERT INTO t1 VALUES (1,2);
 OUTPUT
--------
      1
(1 row)

=> SELECT LOCALTIME time;
    time
-----------------
 15:03:14.595296
(1 row)

=> INSERT INTO t1 VALUES (3,4);
 OUTPUT
--------
      1
(1 row)

=> SELECT LOCALTIME;
    time
-----------------
 15:03:14.595296
(1 row)

=> COMMIT;
COMMIT
=> SELECT LOCALTIME;
    time
-----------------
 15:03:49.738032
(1 row)

2.26 - LOCALTIMESTAMP

Returns a value of type TIMESTAMP/TIMESTAMPTZ that represents the start of the current transaction, and remains unchanged until the transaction is closed.

Returns a value of type TIMESTAMP/TIMESTAMPTZ that represents the start of the current transaction, and remains unchanged until the transaction is closed. Thus, multiple calls to LOCALTIMESTAMP within a given transaction return the same timestamp.

Behavior type

Stable

Syntax

LOCALTIMESTAMP [ ( precision ) ]

Parameters

precision: Rounds the result to the specified number of fractional digits in the seconds field.

Examples

=> CREATE TABLE t1 (a int, b int);
CREATE TABLE
=> INSERT INTO t1 VALUES (1,2);
 OUTPUT
--------
      1
(1 row)

=> SELECT LOCALTIMESTAMP(2) AS 'local timestamp';
    local timestamp
------------------------
 2021-03-05 10:48:58.26
(1 row)

=> INSERT INTO t1 VALUES (3,4);
 OUTPUT
--------
      1
(1 row)

=> SELECT LOCALTIMESTAMP(2) AS 'local timestamp';
    local timestamp
------------------------
 2021-03-05 10:48:58.26
(1 row)

=> COMMIT;
COMMIT
=> SELECT LOCALTIMESTAMP(2) AS 'local timestamp';
    local timestamp
------------------------
 2021-03-05 10:50:08.99
(1 row)

2.27 - MICROSECOND

Returns the microsecond portion of the specified date as an integer.

Behavior type

Immutable if thespecified date is a TIMESTAMP, INTERVAL, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Syntax

MICROSECOND ( date )

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT MICROSECOND (TIMESTAMP 'Sep 22, 2011 12:34:01.123456');
 MICROSECOND
-------------
      123456
(1 row)

2.28 - MIDNIGHT_SECONDS

Within the specified date, returns the number of seconds between midnight and the date's time portion.

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Syntax

MIDNIGHT_SECONDS ( date )

Parameters

date

The date to process, one of the following data types:

Examples

Get the number of seconds since midnight:

=> SELECT MIDNIGHT_SECONDS(CURRENT_TIMESTAMP);
 MIDNIGHT_SECONDS
------------------
            36480
(1 row)

Get the number of seconds between midnight and noon on March 3 2016:

=> SELECT MIDNIGHT_SECONDS('3-3-2016 12:00'::TIMESTAMP);
 MIDNIGHT_SECONDS
------------------
            43200
(1 row)

2.29 - MINUTE

Returns the minute portion of the specified date as an integer.

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, VARCHAR or INTERVAL
Stable if the specified date is aTIMESTAMPTZ

Syntax

MINUTE ( date )

Parameters

date

The date to process, one of the following data types:

INTERVAL

Examples

=> SELECT MINUTE('12:34:03.456789');
 MINUTE
--------
     34
(1 row)
=>SELECT MINUTE (TIMESTAMP 'sep 22, 2011 12:34');
 MINUTE
--------
     34
(1 row)
=> SELECT MINUTE(INTERVAL '35 12:34:03.456789');
 MINUTE
--------
     34
(1 row)

2.30 - MONTH

Returns the month portion of the specified date as an integer.

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, VARCHAR or INTERVAL
Stable if the specified date is aTIMESTAMPTZ

Syntax

MONTH ( date )

Parameters

date

The date to process, one of the following data types:

INTERVAL

Examples

In the following examples, Vertica returns the month portion of the specified string. For example, '6-9' represent September 6.

=> SELECT MONTH('6-9');
 MONTH
-------
     9
(1 row)
=> SELECT MONTH (TIMESTAMP 'sep 22, 2011 12:34');
 MONTH
-------
     9
(1 row)
=> SELECT MONTH(INTERVAL '2-35' year to month);
 MONTH
-------
    11
(1 row)

2.31 - MONTHS_BETWEEN

Returns the number of months between two dates.

Returns the number of months between two dates. MONTHS_BETWEEN can return an integer or a FLOAT:

Integer: The day portions of date1 and date2 are the same, and neither date is the last day of the month. MONTHS_BETWEEN also returns an integer if both dates in date1 and date2 are the last days of their respective months. For example, MONTHS_BETWEEN calculates the difference between April 30 and March 31 as 1 month.
FLOAT: The day portions of date1 and date2 are different and one or both dates are not the last day of their respective months. For example, the difference between April 2 and March 1 is 1.03225806451613. To calculate month fractions, MONTHS_BETWEEN assumes all months contain 31 days.

MONTHS_BETWEEN disregards timestamp time portions.

Behavior type

Immutable if both date arguments are of data type TIMESTAMP or DATE
Stable if either date is a TIMESTAMPTZ

Syntax

MONTHS_BETWEEN ( date1 , date2 );

Parameters

date1 date2

Specify the dates to evaluate where date1 and date2 evaluate to one of the following data types:

DATE
TIMESTAMP
TIMESTAMPTZ

If date1 < date2, MONTHS_BETWEEN returns a negative value.

Examples

Return the number of months between April 7 2016 and January 7 2015:

=> SELECT MONTHS_BETWEEN ('04-07-16'::TIMESTAMP, '01-07-15'::TIMESTAMP);
 MONTHS_BETWEEN
----------------
             15
(1 row)

Return the number of months between March 31 2016 and February 28 2016 (MONTHS_BETWEEN assumes both months contain 31 days):

=> SELECT MONTHS_BETWEEN ('03-31-16'::TIMESTAMP, '02-28-16'::TIMESTAMP);
  MONTHS_BETWEEN
------------------
 1.09677419354839
(1 row)

Return the number of months between March 31 2016 and February 29 2016:

=> SELECT MONTHS_BETWEEN ('03-31-16'::TIMESTAMP, '02-29-16'::TIMESTAMP);
 MONTHS_BETWEEN
----------------
              1
(1 row)

2.32 - NEW_TIME

Converts a timestamp value from one time zone to another and returns a TIMESTAMP.

Behavior type

Immutable

Syntax

NEW_TIME( 'timestamp' , 'timezone1' , 'timezone2')

Parameters

timestamp

The timestamp to convert, conforms to one of the following formats:

TIMESTAMP/TIMESTAMPTZ
DATE
Character string that can be converted to a TIMESTAMP—for example, May 24, 2012 10:00.

timezone1 *`timezone2`*

Specify the source and target timezones, one of the strings defined in /opt/vertica/share/timezonesets. For example:

GMT: Greenwich Mean Time
AST / ADT: Atlantic Standard/Daylight Time
EST / EDT: Eastern Standard/Daylight Time
CST / CDT: Central Standard/Daylight Time
MST / MDT: Mountain Standard/Daylight Time
PST / PDT: Pacific Standard/Daylight Time

Examples

Convert the specified time from Eastern Standard Time (EST) to Pacific Standard Time (PST):

=> SELECT NEW_TIME('05-24-12 13:48:00', 'EST', 'PST');
      NEW_TIME
---------------------
 2012-05-24 10:48:00
(1 row)

Convert 1:00 AM January 2012 from EST to PST:

=> SELECT NEW_TIME('01-01-12 01:00:00', 'EST', 'PST');
      NEW_TIME
---------------------
 2011-12-31 22:00:00
(1 row)

Convert the current time EST to PST:


=> SELECT NOW();
              NOW
-------------------------------
 2016-12-09 10:30:36.727307-05
(1 row)

=> SELECT NEW_TIME('NOW', 'EDT', 'CDT');
          NEW_TIME
----------------------------
 2016-12-09 09:30:36.727307
(1 row)

The following example returns the year 45 before the Common Era in Greenwich Mean Time and converts it to Newfoundland Standard Time:

=>  SELECT NEW_TIME('April 1, 45 BC', 'GMT', 'NST')::DATE;
   NEW_TIME
---------------
 0045-03-31 BC
(1 row)

2.33 - NEXT_DAY

Returns the date of the first instance of a particular day of the week that follows the specified date.

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Syntax

NEXT_DAY( 'date', 'day-string')

Parameters

date

The date to process, one of the following data types:

day-string

The day of the week to process, a CHAR or VARCHAR string or character constant. Supply the full English name such as Tuesday, or any conventional abbreviation, such as Tue or Tues. day-string is not case sensitive and trailing spaces are ignored.

Examples

Get the date of the first Monday that follows April 29 2016:

=> SELECT NEXT_DAY('4-29-2016'::TIMESTAMP,'Monday') "NEXT DAY" ;
  NEXT DAY
------------
 2016-05-02
(1 row)

Get the first Tuesday that follows today:

SELECT NEXT_DAY(CURRENT_TIMESTAMP,'tues') "NEXT DAY" ;
  NEXT DAY
------------
 2016-05-03
(1 row)

2.34 - NOW [date/time]

Returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction.

Returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction. NOW is equivalent to CURRENT_TIMESTAMP except that it does not accept a precision parameter.

The return value does not change during the transaction. Thus, multiple calls to CURRENT_TIMESTAMP within the same transaction return the same timestamp.

Behavior type

Stable

Syntax

NOW()

Examples


=> CREATE TABLE t1 (a int, b int);
CREATE TABLE
=> INSERT INTO t1 VALUES (1,2);
 OUTPUT
--------
      1
(1 row)

=> SELECT NOW();
             NOW
------------------------------
 2016-12-09 13:00:08.74685-05
(1 row)

=> INSERT INTO t1 VALUES (3,4);
 OUTPUT
--------
      1
(1 row)

=> SELECT NOW();
             NOW
------------------------------
 2016-12-09 13:00:08.74685-05
(1 row)

=> COMMIT;
COMMIT
dbadmin=> SELECT NOW();
              NOW
-------------------------------
 2016-12-09 13:01:31.420624-05
(1 row)

2.35 - OVERLAPS

Evaluates two time periods and returns true when they overlap, false otherwise.

Behavior type

Stable when TIMESTAMP and TIMESTAMPTZ are both used, or when TIMESTAMPTZ is used with INTERVAL
Immutable otherwise

Syntax

( start, end ) OVERLAPS ( start, end )
( start, interval) OVERLAPS ( start, interval )

Parameters

start: DATE, TIME, or TIMESTAMP/TIMESTAMPTZ value that specifies the beginning of a time period.
end: DATE, TIME, or TIMESTAMP/TIMESTAMPTZ value that specifies the end of a time period.
interval: Value that specifies the length of the time period.

Examples

Evaluate whether date ranges Feb 16 - Dec 21, 2016 and Oct 10 2008 - Oct 3 2016 overlap:

=> SELECT (DATE '2016-02-16', DATE '2016-12-21') OVERLAPS (DATE '2008-10-30', DATE '2016-10-30');
 overlaps
----------
 t
(1 row)

Evaluate whether date ranges Feb 16 - Dec 21, 2016 and Jan 01 - Oct 30 2008 - Oct 3, 2016 overlap:

=> SELECT (DATE '2016-02-16', DATE '2016-12-21') OVERLAPS (DATE '2008-01-30', DATE '2008-10-30');
 overlaps
----------
 f
(1 row)

Evaluate whether date range Feb 02 2016 + 1 week overlaps with date range Oct 16 2016 - 8 months:

=> SELECT (DATE '2016-02-16', INTERVAL '1 week') OVERLAPS (DATE '2016-10-16', INTERVAL '-8 months');
 overlaps
----------
 t
(1 row)

2.36 - QUARTER

Returns calendar quarter of the specified date as an integer, where the January-March quarter is 1.

Syntax

QUARTER ( date )

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR.
Stable if the specified date is aTIMESTAMPTZ

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT QUARTER (TIMESTAMP 'sep 22, 2011 12:34');
 QUARTER
---------
       3
(1 row)

2.37 - ROUND

Rounds the specified date or time.

Rounds the specified date or time. If you omit the precision argument, ROUND rounds to day (DD) precision.

Behavior type

Immutable if thetarget date is a TIMESTAMP or DATE
Stable if the target date is aTIMESTAMPTZ

Syntax

ROUND( rounding-target[, 'precision'] )

Parameters

*rounding-target*

An expression that evaluates to one of the following data types:

precision

A string constant that specifies precision for the rounded value, one of the following:

Century: CC | SCC
Year: SYYY | YYYY | YEAR | YYY | YY | Y
ISO Year: IYYY | IYY | IY | I
Quarter: Q
Month: MONTH | MON | MM | RM
Same weekday as first day of year: WW
Same weekday as first day of ISO year: IW
Same weekday as first day of month: W
Day (default): DDD | DD | J
First weekday: DAY | DY | D
Hour: HH | HH12 | HH24
Minute: MI
Second: SS

Note

Hour, minute, and second rounding is not supported by DATE expressions.

Examples

Round to the nearest hour:

=> SELECT ROUND(CURRENT_TIMESTAMP, 'HH');
        ROUND
---------------------
 2016-04-28 15:00:00
(1 row)

Round to the nearest month:

=> SELECT ROUND('9-22-2011 12:34:00'::TIMESTAMP, 'MM');
        ROUND
---------------------
 2011-10-01 00:00:00
(1 row)

2.38 - SECOND

Returns the seconds portion of the specified date as an integer.

Syntax

SECOND ( date )

Behavior type

Immutable, except for TIMESTAMPTZ arguments where it is stable.

Parameters

date

The date to process, one of the following data types:

Examples

=> SELECT SECOND ('23:34:03.456789');
 SECOND
--------
      3
(1 row)
=> SELECT SECOND (TIMESTAMP 'sep 22, 2011 12:34');
 SECOND
--------
      0
(1 row)
=> SELECT SECOND (INTERVAL '35 12:34:03.456789');
 SECOND
--------
      3
(1 row)

2.39 - STATEMENT_TIMESTAMP

Similar to TRANSACTION_TIMESTAMP, returns a value of type TIMESTAMP WITH TIME ZONE that represents the start of the current statement.

Similar to TRANSACTION_TIMESTAMP, returns a value of type TIMESTAMP WITH TIME ZONE that represents the start of the current statement.

The return value does not change during statement execution. Thus, different stages of statement execution always have the same timestamp.

Behavior type

Stable

Syntax

STATEMENT_TIMESTAMP()

Examples

=> SELECT foo, bar FROM (SELECT STATEMENT_TIMESTAMP() AS foo)foo, (SELECT STATEMENT_TIMESTAMP() as bar)bar;
              foo              |              bar
-------------------------------+-------------------------------
 2016-12-07 14:55:51.543988-05 | 2016-12-07 14:55:51.543988-05
(1 row)

2.40 - SYSDATE

Returns the current statement's start date and time as a TIMESTAMP value.

Returns the current statement's start date and time as a TIMESTAMP value. This function is identical to GETDATE.

SYSDATE uses the date and time supplied by the operating system on the server to which you are connected, which is the same across all servers. Internally, GETDATE converts STATEMENT_TIMESTAMP from TIMESTAMPTZ to TIMESTAMP.

Behavior type

Stable

Syntax

SYSDATE()

Note

You can call this function with no parentheses.

Examples

=> SELECT SYSDATE;
          sysdate
----------------------------
 2016-12-12 06:11:10.699642
(1 row)

2.41 - TIME_SLICE

Aggregates data by different fixed-time intervals and returns a rounded-up input TIMESTAMP value to a value that corresponds with the start or end of the time slice interval.

Aggregates data by different fixed-time intervals and returns a rounded-up input TIMESTAMP value to a value that corresponds with the start or end of the time slice interval.

Given an input TIMESTAMP value such as 2000-10-28 00:00:01, the start time of a 3-second time slice interval is 2000-10-28 00:00:00, and the end time of the same time slice is 2000-10-28 00:00:03.

Behavior type

Immutable

Syntax

TIME_SLICE( expression, slice-length [, 'time-unit' [, 'start-or-end' ] ] )

Parameters

expression

One of the following:

Column of type TIMESTAMP
String constant that can be parsed into a TIMESTAMP value. For example:

'2004-10-19 10:23:54'

Vertica evaluates expression on each row.

slice-length

A positive integer that specifies the slice length.

time-unit

Time unit of the slice, one of the following:

HOUR
MINUTE
SECOND (default)
MILLISECOND
MICROSECOND

start-or-end

Specifies whether the returned value corresponds to the start or end time with one of the following strings:

START (default)
END

Note

This parameter can be included only if you also supply a non-null time-unit argument.

Null argument handling

TIME_SLICE handles null arguments as follows:

TIME_SLICE returns an error when any one of slice-length, time-unit, or start-or-end parameters is null.
If expression is null and *slice-length*, *time-unit*, or *start-or-end* contain legal values, TIME_SLICE returns a NULL value instead of an error.

Usage

The following command returns the (default) start time of a 3-second time slice:

=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3);
     TIME_SLICE
---------------------
 2009-09-19 00:00:00
(1 row)

The following command returns the end time of a 3-second time slice:

=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3, 'SECOND', 'END');
     TIME_SLICE
---------------------
 2009-09-19 00:00:03
(1 row)

This command returns results in milliseconds, using a 3-second time slice:

=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3, 'ms');
       TIME_SLICE
-------------------------
 2009-09-19 00:00:00.999

(1 row)

This command returns results in microseconds, using a 9-second time slice:

=> SELECT TIME_SLICE('2009-09-19 00:00:01', 3, 'us');
         TIME_SLICE
----------------------------
 2009-09-19 00:00:00.999999
(1 row)

The next example uses a 3-second interval with an input value of '00:00:01'. To focus specifically on seconds, the example omits date, though all values are implied as being part of the timestamp with a given input of '00:00:01':

'00:00:00' is the start of the 3-second time slice
'00:00:03' is the end of the 3-second time slice.
'00:00:03' is also the start of the second 3-second time slice. In time slice boundaries, the end value of a time slice does not belong to that time slice; it starts the next one.

When the time slice interval is not a factor of 60 seconds, such as a given slice length of 9 in the following example, the slice does not always start or end on 00 seconds:

=> SELECT TIME_SLICE('2009-02-14 20:13:01', 9);
     TIME_SLICE
---------------------
 2009-02-14 20:12:54
(1 row)

This is expected behavior, as the following properties are true for all time slices:

Equal in length
Consecutive (no gaps between them)
Non-overlapping

To force the above example ('2009-02-14 20:13:01') to start at '2009-02-14 20:13:00', adjust the output timestamp values so that the remainder of 54 counts up to 60:

=> SELECT TIME_SLICE('2009-02-14 20:13:01', 9 )+'6 seconds'::INTERVAL AS time;
        time
---------------------
 2009-02-14 20:13:00
(1 row)

Alternatively, you could use a different slice length, which is divisible by 60, such as 5:

=> SELECT TIME_SLICE('2009-02-14 20:13:01', 5);
     TIME_SLICE
---------------------
 2009-02-14 20:13:00
(1 row)

A TIMESTAMPTZ value is implicitly cast to TIMESTAMP. For example, the following two statements have the same effect.

=> SELECT TIME_SLICE('2009-09-23 11:12:01'::timestamptz, 3);
     TIME_SLICE
---------------------
 2009-09-23 11:12:00
(1 row)


=> SELECT TIME_SLICE('2009-09-23 11:12:01'::timestamptz::timestamp, 3);

     TIME_SLICE
---------------------
 2009-09-23 11:12:00
(1 row)

Examples

You can use the SQL analytic functions FIRST_VALUE and LAST_VALUE to find the first/last price within each time slice group (set of rows belonging to the same time slice). This structure can be useful if you want to sample input data by choosing one row from each time slice group.

=> SELECT date_key, transaction_time, sales_dollar_amount,TIME_SLICE(DATE '2000-01-01' + date_key + transaction_time, 3),
FIRST_VALUE(sales_dollar_amount)
OVER (PARTITION BY TIME_SLICE(DATE '2000-01-01' + date_key + transaction_time, 3)
     ORDER BY DATE '2000-01-01' + date_key + transaction_time) AS first_value
FROM store.store_sales_fact
LIMIT 20;

 date_key | transaction_time | sales_dollar_amount |     time_slice      | first_value
----------+------------------+---------------------+---------------------+-------------
        1 | 00:41:16         |                 164 | 2000-01-02 00:41:15 |         164
        1 | 00:41:33         |                 310 | 2000-01-02 00:41:33 |         310
        1 | 15:32:51         |                 271 | 2000-01-02 15:32:51 |         271
        1 | 15:33:15         |                 419 | 2000-01-02 15:33:15 |         419
        1 | 15:33:44         |                 193 | 2000-01-02 15:33:42 |         193
        1 | 16:36:29         |                 466 | 2000-01-02 16:36:27 |         466
        1 | 16:36:44         |                 250 | 2000-01-02 16:36:42 |         250
        2 | 03:11:28         |                  39 | 2000-01-03 03:11:27 |          39
        3 | 03:55:15         |                 375 | 2000-01-04 03:55:15 |         375
        3 | 11:58:05         |                 369 | 2000-01-04 11:58:03 |         369
        3 | 11:58:24         |                 174 | 2000-01-04 11:58:24 |         174
        3 | 11:58:52         |                 449 | 2000-01-04 11:58:51 |         449
        3 | 19:01:21         |                 201 | 2000-01-04 19:01:21 |         201
        3 | 22:15:05         |                 156 | 2000-01-04 22:15:03 |         156
        4 | 13:36:57         |                -125 | 2000-01-05 13:36:57 |        -125
        4 | 13:37:24         |                -251 | 2000-01-05 13:37:24 |        -251
        4 | 13:37:54         |                 353 | 2000-01-05 13:37:54 |         353
        4 | 13:38:04         |                 426 | 2000-01-05 13:38:03 |         426
        4 | 13:38:31         |                 209 | 2000-01-05 13:38:30 |         209
        5 | 10:21:24         |                 488 | 2000-01-06 10:21:24 |         488
(20 rows)

TIME_SLICE rounds the transaction time to the 3-second slice length.

The following example uses the analytic (window) OVER clause to return the last trading price (the last row ordered by TickTime) in each 3-second time slice partition:

=> SELECT DISTINCT TIME_SLICE(TickTime, 3), LAST_VALUE(price)OVER (PARTITION BY TIME_SLICE(TickTime, 3)
ORDER BY TickTime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

Note

If you omit the windowing clause from an analytic clause, LAST_VALUE defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Results can seem non-intuitive, because instead of returning the value from the bottom of the current partition, the function returns the bottom of the window, which continues to change along with the current input row that is being processed. For more information, see Time series analytics and SQL analytics.

In the next example, FIRST_VALUE is evaluated once for each input record and the data is sorted by ascending values. Use SELECT DISTINCT to remove the duplicates and return only one output record per TIME_SLICE:

=> SELECT DISTINCT TIME_SLICE(TickTime, 3), FIRST_VALUE(price)OVER (PARTITION BY TIME_SLICE(TickTime, 3)
ORDER BY TickTime ASC)
FROM tick_store;
     TIME_SLICE      | ?column?
---------------------+----------
 2009-09-21 00:00:06 |    20.00
 2009-09-21 00:00:09 |    30.00
 2009-09-21 00:00:00 |    10.00
(3 rows)

The information output by the above query can also return MIN, MAX, and AVG of the trading prices within each time slice.

=> SELECT DISTINCT TIME_SLICE(TickTime, 3),FIRST_VALUE(Price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)
ORDER BY TickTime ASC),
  MIN(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)),
  MAX(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3)),
  AVG(price) OVER (PARTITION BY TIME_SLICE(TickTime, 3))
FROM tick_store;

2.42 - TIMEOFDAY

Returns the wall-clock time as a text string.

Returns the wall-clock time as a text string. Function results advance during transactions.

Behavior type

Volatile

Syntax

TIMEOFDAY()

Examples

=> SELECT TIMEOFDAY();
              TIMEOFDAY
-------------------------------------
 Mon Dec 12 08:18:01.022710 2016 EST
(1 row)

2.43 - TIMESTAMP_ROUND

Rounds the specified TIMESTAMP.

Rounds the specified TIMESTAMP. If you omit the precision argument, TIMESTAMP_ROUND rounds to day (DD) precision.

Behavior type

Immutable if thetarget date is a TIMESTAMP
Stable if the target date is a TIMESTAMPTZ

Syntax

TIMESTAMP_ROUND ( rounding-target[, 'precision'] )

Parameters

rounding-target

An expression that evaluates to one of the following data types:

precision

A string constant that specifies precision for the rounded value, one of the following:

Century: CC | SCC
Year: SYYY | YYYY | YEAR | YYY | YY | Y
ISO Year: IYYY | IYY | IY | I
Quarter: Q
Month: MONTH | MON | MM | RM
Same weekday as first day of year: WW
Same weekday as first day of ISO year: IW
Same weekday as first day of month: W
Day (default): DDD | DD | J
First weekday: DAY | DY | D
Hour: HH | HH12 | HH24
Minute: MI
Second: SS

Note

Hour, minute, and second rounding is not supported by DATE expressions.

Examples

Round to the nearest hour:

=> SELECT TIMESTAMP_ROUND(CURRENT_TIMESTAMP, 'HH');
        ROUND
---------------------
 2016-04-28 15:00:00
(1 row)

Round to the nearest month:

=> SELECT TIMESTAMP_ROUND('9-22-2011 12:34:00'::TIMESTAMP, 'MM');
        ROUND
---------------------
 2011-10-01 00:00:00
(1 row)

2.44 - TIMESTAMP_TRUNC

Truncates the specified TIMESTAMP.

Truncates the specified TIMESTAMP. If you omit the precision argument, TIMESTAMP_TRUNC truncates to day (DD) precision.

Behavior type

Immutable if thetarget date is a TIMESTAMP
Stable if the target date is a TIMESTAMPTZ

Syntax

TIMESTAMP_TRUNC( trunc-target[, 'precision'] )

Parameters

trunc-target

An expression that evaluates to one of the following data types:

precision

A string constant that specifies precision for the truncated value, one of the following:

Century: CC | SCC
Year: SYYY | YYYY | YEAR | YYY | YY | Y
ISO Year: IYYY | IYY | IY | I
Quarter: Q
Month: MONTH | MON | MM | RM
Same weekday as first day of year: WW
Same weekday as first day of ISO year: IW
Same weekday as first day of month: W
Day: DDD | DD | J
First weekday: DAY | DY | D
Hour: HH | HH12 | HH24
Minute: MI
Second: SS

Note

Hour, minute, and second truncating is not supported by DATE expressions.

Examples

Truncate to the current hour:

=> SELECT TIMESTAMP_TRUNC(CURRENT_TIMESTAMP, 'HH');
   TIMESTAMP_TRUNC
---------------------
 2016-04-29 08:00:00
(1 row)

Truncate to the month:

=> SELECT TIMESTAMP_TRUNC('9-22-2011 12:34:00'::TIMESTAMP, 'MM');
   TIMESTAMP_TRUNC
---------------------
 2011-09-01 00:00:00
(1 row)

2.45 - TIMESTAMPADD

Adds the specified number of intervals to a TIMESTAMP or TIMESTAMPTZ value and returns a result of the same data type.

Behavior type

Immutable if the input date is a TIMESTAMP
Stable if the input date is a TIMESTAMPTZ

Syntax

TIMESTAMPADD ( datepart, count, start-date );

Parameters

datepart

Specifies the type of time intervals that TIMESTAMPADD adds to the specified start date. If datepart is an expression, it must be enclosed in parentheses:

TIMESTAMPADD((expression), interval, start;

datepart must evaluate to one of the following string literals, either quoted or unquoted:

year | yy | yyyy
quarter | qq | q
month | mm | m
day | dayofyear | dd | d | dy | y
week | wk | ww
hour | hh
minute | mi | n
second | ss | s
millisecond | ms
microsecond | mcs | us

count

Integer or integer expression that specifies the number of datepart intervals to add to start-date.

start-date

TIMESTAMP or TIMESTAMPTZ value.

Examples

Add two months to the current date:

=> SELECT CURRENT_TIMESTAMP AS Today;
           Today
-------------------------------
 2016-05-02 06:56:57.923045-04
(1 row)

=> SELECT TIMESTAMPADD (MONTH, 2, (CURRENT_TIMESTAMP)) AS TodayPlusTwoMonths;;
      TodayPlusTwoMonths
-------------------------------
 2016-07-02 06:56:57.923045-04
(1 row)

Add 14 days to the beginning of the current month:

=> SELECT TIMESTAMPADD (DD, 14, (SELECT TRUNC((CURRENT_TIMESTAMP), 'MM')));
    timestampadd
---------------------
 2016-05-15 00:00:00
(1 row)

2.46 - TIMESTAMPDIFF

Returns the time span between two TIMESTAMP or TIMESTAMPTZ values, in the intervals specified.

Returns the time span between two TIMESTAMP or TIMESTAMPTZ values, in the intervals specified. TIMESTAMPDIFF excludes the start date in its calculation.

Behavior type

Immutable if start and end dates are TIMESTAMP
Stable if start and end dates are TIMESTAMPTZ

Syntax

TIMESTAMPDIFF ( datepart, start, end );

Parameters

datepart

Specifies the type of date or time intervals that TIMESTAMPDIFF returns. If datepart is an expression, it must be enclosed in parentheses:

TIMESTAMPDIFF((expression), start, end );

datepart must evaluate to one of the following string literals, either quoted or unquoted:

year | yy | yyyy
quarter | qq | q
month | mm | m
day | dayofyear | dd | d | dy | y
week | wk | ww
hour | hh
minute | mi | n
second | ss | s
millisecond | ms
microsecond | mcs | us

start, end

Specify the start and end dates, where start and end evaluate to one of the following data types:

TIMESTAMP/TIMESTAMPTZ
TIMESTAMPTZ

If end < start, TIMESTAMPDIFF returns a negative value.

Date part intervals

TIMESTAMPDIFF uses the datepart argument to calculate the number of intervals between two dates, rather than the actual amount of time between them. For detailed information, see DATEDIFF.

Examples

=> SELECT TIMESTAMPDIFF (YEAR,'1-1-2006 12:34:00', '1-1-2008 12:34:00');
 timestampdiff
---------------
             2
(1 row)

2.47 - TRANSACTION_TIMESTAMP

Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.

Returns a value of type `TIME WITH TIMEZONE` that represents the start of the current transaction.

The return value does not change during the transaction. Thus, multiple calls to TRANSACTION_TIMESTAMP within the same transaction return the same timestamp.

TRANSACTION_TIMESTAMP is equivalent to CURRENT_TIMESTAMP, except it does not accept a precision parameter.

Behavior type

Stable

Syntax

TRANSACTION_TIMESTAMP()

Examples

=> SELECT foo, bar FROM (SELECT TRANSACTION_TIMESTAMP() AS foo)foo, (SELECT TRANSACTION_TIMESTAMP() as bar)bar;
              foo              |              bar
-------------------------------+-------------------------------
 2016-12-12 08:18:00.988528-05 | 2016-12-12 08:18:00.988528-05
(1 row)

2.48 - TRUNC

Truncates the specified date or time.

Truncates the specified date or time. If you omit the precision argument, TRUNC truncates to day (DD) precision.

Behavior type

Immutable if thetarget date is a TIMESTAMP or DATE
Stable if the target date is aTIMESTAMPTZ

Syntax

TRUNC( trunc-target[, 'precision'] )

Parameters

*trunc-target*

An expression that evaluates to one of the following data types:

precision

A string constant that specifies precision for the truncated value, one of the following:

Century: CC | SCC
Year: SYYY | YYYY | YEAR | YYY | YY | Y
ISO Year: IYYY | IYY | IY | I
Quarter: Q
Month: MONTH | MON | MM | RM
Same weekday as first day of year: WW
Same weekday as first day of ISO year: IW
Same weekday as first day of month: W
Day (default): DDD | DD | J
First weekday: DAY | DY | D
Hour: HH | HH12 | HH24
Minute: MI
Second: SS

Note

Hour, minute, and second truncating is not supported by DATE expressions.

Examples

Truncate to the current hour:

=> => SELECT TRUNC(CURRENT_TIMESTAMP, 'HH');
        TRUNC
---------------------
 2016-04-29 10:00:00
(1 row)

Truncate to the month:

=> SELECT TRUNC('9-22-2011 12:34:00'::TIMESTAMP, 'MM');
   TIMESTAMP_TRUNC
---------------------
 2011-09-01 00:00:00
(1 row)

2.49 - WEEK

Returns the week of the year for the specified date as an integer, where the first week begins on the first Sunday on or preceding January 1.

Syntax

WEEK ( date )

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Parameters

date

The date to process, one of the following data types:

Examples

January 2 is on Saturday, so WEEK returns 1:

=> SELECT WEEK ('1-2-2016'::DATE);
 WEEK
------
    1
(1 row)

January 3 is the second Sunday in 2016, so WEEK returns 2:

=> SELECT WEEK ('1-3-2016'::DATE);
 WEEK
------
    2
(1 row)

2.50 - WEEK_ISO

Returns the week of the year for the specified date as an integer, where the first week starts on Monday and contains January 4.

Returns the week of the year for the specified date as an integer, where the first week starts on Monday and contains January 4. This function conforms with the ISO 8061 standard.

Syntax

WEEK_ISO ( date )

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Parameters

date

The date to process, one of the following data types:

Examples

The first week of 2016 begins on Monday January 4:

=> SELECT WEEK_ISO ('1-4-2016'::DATE);
 WEEK_ISO
----------
        1
(1 row)

January 3 2016 returns week 53 of the previous year (2015):

=> SELECT WEEK_ISO ('1-3-2016'::DATE);
 WEEK_ISO
----------
       53
(1 row)

In 2015, January 4 is on Sunday, so the first week of 2015 begins on the preceding Monday (December 29 2014):

=> SELECT WEEK_ISO ('12-29-2014'::DATE);
 WEEK_ISO
----------
        1
(1 row)

2.51 - YEAR

Returns an integer that represents the year portion of the specified date.

Syntax

YEAR( date )

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, VARCHAR, or INTERVAL
Stable if the specified date is aTIMESTAMPTZ

Parameters

date

The date to process, one of the following data types:

INTERVAL

Examples

=> SELECT YEAR(CURRENT_DATE::DATE);
 YEAR
------
 2016
(1 row)

2.52 - YEAR_ISO

Returns an integer that represents the year portion of the specified date.

Returns an integer that represents the year portion of the specified date. The return value is based on the ISO 8061 standard.

The first week of the ISO year is the week that contains January 4.

Syntax

YEAR_ISO ( date )

Behavior type

Immutable if thespecified date is a TIMESTAMP, DATE, or VARCHAR
Stable if the specified date is aTIMESTAMPTZ

Parameters

date

The date to process, one of the following data types:

Examples

> SELECT YEAR_ISO(CURRENT_DATE::DATE);
 YEAR_ISO
----------
     2016
(1 row)

3 - IP address functions

IP functions perform conversion, calculation, and manipulation operations on IP, network, and subnet addresses.

3.1 - INET_ATON

Converts a string that contains a dotted-quad representation of an IPv4 network address to an INTEGER.

Converts a string that contains a dotted-quad representation of an IPv4 network address to an INTEGER. It trims any surrounding white space from the string. This function returns NULL if the string is NULL or contains anything other than a quad dotted IPv4 address.

Behavior type

Immutable

Syntax

INET_ATON ( expression )

Arguments

expression: the string to convert.

Examples

=> SELECT INET_ATON('209.207.224.40');
 inet_aton
------------
 3520061480
(1 row)

=> SELECT INET_ATON('1.2.3.4');
 inet_aton
-----------
  16909060
(1 row)

=> SELECT TO_HEX(INET_ATON('1.2.3.4'));
 to_hex
---------
 1020304
(1 row)

3.2 - INET_NTOA

Converts an INTEGER value into a VARCHAR dotted-quad representation of an IPv4 network address.

Converts an INTEGER value into a VARCHAR dotted-quad representation of an IPv4 network address. INET_NTOA returns NULL if the integer value is NULL, negative, or is greater than 2³² (4294967295).

Behavior type

Immutable

Syntax

INET_NTOA ( expression )

Arguments

expression: The integer network address to convert.

Examples

=> SELECT INET_NTOA(16909060);
 inet_ntoa
-----------
 1.2.3.4
(1 row)

=> SELECT INET_NTOA(03021962);
 inet_ntoa
-------------
 0.46.28.138
(1 row)

3.3 - V6_ATON

Converts a string containing a colon-delimited IPv6 network address into a VARBINARY string.

Converts a string containing a colon-delimited IPv6 network address into a VARBINARY string. Any spaces around the IPv6 address are trimmed. This function returns NULL if the input value is NULL or it cannot be parsed as an IPv6 address. This function relies on the Linux function inet_pton.

Behavior type

Immutable

Syntax

V6_ATON ( expression )

Arguments

expression: (VARCHAR) the string containing an IPv6 address to convert.

Examples

=> SELECT V6_ATON('2001:DB8::8:800:200C:417A');
                       v6_aton
------------------------------------------------------
  \001\015\270\000\000\000\000\000\010\010\000 \014Az
(1 row)

=> SELECT V6_ATON('1.2.3.4');
              v6_aton
------------------------------------------------------------------
 \000\000\000\000\000\000\000\000\000\000\377\377\001\002\003\004
(1 row)
SELECT TO_HEX(V6_ATON('2001:DB8::8:800:200C:417A'));
              to_hex
----------------------------------
 20010db80000000000080800200c417a
(1 row)

=> SELECT V6_ATON('::1.2.3.4');
              v6_aton
------------------------------------------------------------------
 \000\000\000\000\000\000\000\000\000\000\000\000\001\002\003\004
(1 row)

3.4 - V6_NTOA

Converts an IPv6 address represented as varbinary to a character string.

Behavior type

Immutable

Syntax

V6_NTOA ( expression )

Arguments

expression: (VARBINARY) is the binary string to convert.

Notes

The following syntax converts an IPv6 address represented as VARBINARY B to a string A.

V6_NTOA right-pads B to 16 bytes with zeros, if necessary, and calls the Linux function inet_ntop.

=> V6_NTOA(VARBINARY B) -> VARCHAR A

If B is NULL or longer than 16 bytes, the result is NULL.

Vertica automatically converts the form '::ffff:1.2.3.4' to '1.2.3.4'.

Examples

=> SELECT V6_NTOA(' \001\015\270\000\000\000\000\000\010\010\000 \014Az');
          v6_ntoa
---------------------------
 2001:db8::8:800:200c:417a
(1 row)

=> SELECT V6_NTOA(V6_ATON('1.2.3.4'));
 v6_ntoa
---------
 1.2.3.4
(1 row)

=> SELECT V6_NTOA(V6_ATON('::1.2.3.4'));
  v6_ntoa
-----------
 ::1.2.3.4
(1 row)

3.5 - V6_SUBNETA

Returns a VARCHAR containing a subnet address in CIDR (Classless Inter-Domain Routing) format from a binary or alphanumeric IPv6 address.

Returns a VARCHAR containing a subnet address in CIDR (Classless Inter-Domain Routing) format from a binary or alphanumeric IPv6 address. Returns NULL if either parameter is NULL, the address cannot be parsed as an IPv6 address, or the subnet value is outside the range of 0 to 128.

Behavior type

Immutable

Syntax

V6_SUBNETA ( address, subnet)

Arguments

address: VARBINARY or VARCHAR containing the IPv6 address.
subnet: The size of the subnet in bits as an INTEGER. This value must be greater than zero and less than or equal to 128.

Examples

=> SELECT V6_SUBNETA(V6_ATON('2001:db8::8:800:200c:417a'), 28);
  v6_subneta
---------------
 2001:db0::/28
(1 row)

3.6 - V6_SUBNETN

Calculates a subnet address in CIDR (Classless Inter-Domain Routing) format from a varbinary or alphanumeric IPv6 address.

Behavior type

Immutable

Syntax

V6_SUBNETN ( address, subnet-size)

Arguments

address: The IPv6 address as a VARBINARY or VARCHAR. The format you pass in determines the date type of the output. If you pass in a VARBINARY address, V6_SUBNETN returns a VARBINARY value. If you pass in a VARCHAR value, it returns a VARCHAR.
subnet-size: The size of the subnet as an INTEGER.

Notes

The following syntax masks a BINARY IPv6 address B so that the N left-most bits of S form a subnet address, while the remaining right-most bits are cleared.

V6_SUBNETN right-pads B to 16 bytes with zeros, if necessary and masks B, preserving its N-bit subnet prefix.

=> V6_SUBNETN(VARBINARY B, INT8 N) -> VARBINARY(16) S

If B is NULL or longer than 16 bytes, or if N is not between 0 and 128 inclusive, the result is NULL.

S = [B]/N in Classless Inter-Domain Routing notation (CIDR notation).

The following syntax masks an alphanumeric IPv6 address A so that the N leftmost bits form a subnet address, while the remaining rightmost bits are cleared.

=> V6_SUBNETN(VARCHAR A, INT8 N) -> V6_SUBNETN(V6_ATON(A), N) -> VARBINARY(16) S

Examples

This example returns VARBINARY, after using V6_ATON to convert the VARCHAR string to VARBINARY:

=> SELECT V6_SUBNETN(V6_ATON('2001:db8::8:800:200c:417a'), 28);
                           v6_subnetn
---------------------------------------------------------------
  \001\015\260\000\000\000\000\000\000\000\000\000\000\000\000

3.7 - V6_TYPE

Returns an INTEGER value that classifies the type of the network address passed to it as defined in IETF RFC 4291 section 2.4.

Returns an INTEGER value that classifies the type of the network address passed to it as defined in IETF RFC 4291 section 2.4. For example, If you pass this function the string 127.0.0.1, it returns 2 which indicates the address is a loopback address. This function accepts both IPv4 and IPv6 addresses.

Behavior type

Immutable

Syntax

V6_TYPE ( address)

Arguments

address: A VARBINARY or VARCHAR containing an IPv6 or IPv4 address to describe.

Returns

The values returned by this function are:

Return Value	Address Type	Description
0	GLOBAL	Global unicast addresses
1	LINKLOCAL	Link-Local unicast (and private-use) addresses
2	LOOPBACK	Loopback addresses
3	UNSPECIFIED	Unspecifiedaddresses
4	MULTICAST	Multicastaddresses

The return value is based on the following table of IP address ranges:

Address Family	CIDR	Type
IPv4	0.0.0.0/8	UNSPECIFIED
	10.0.0.0/8	LINKLOCAL
	127.0.0.0/8	LOOPBACK
	169.254.0.0/16	LINKLOCAL
	172.16.0.0/12	LINKLOCAL
	192.168.0.0/16	LINKLOCAL
	224.0.0.0/4	MULTICAST
	All other addresses	GLOBAL
IPv6	::0/128	UNSPECIFIED
	::1/128	LOOPBACK
	fe80::/10	LINKLOCAL
	ff00::/8	MULTICAST
	All other addresses	GLOBAL

This function returns NULL if you pass it a NULL value or an invalid address.

Examples

=> SELECT V6_TYPE(V6_ATON('192.168.2.10'));
 v6_type
---------
       1
(1 row)

=> SELECT V6_TYPE(V6_ATON('2001:db8::8:800:200c:417a'));
 v6_type
---------
       0
(1 row)

4 - Sequence functions

The sequence functions provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.

4.1 - CURRVAL

Returns the last value across all nodes that was set by NEXTVAL on this sequence in the current session.

Returns the last value across all nodes that was set by NEXTVAL on this sequence in the current session. If NEXTVAL was never called on this sequence since its creation, Vertica returns an error.

Syntax

CURRVAL ('[[database.]schema.]sequence-name')

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
sequence-name: The target sequence

Privileges

SELECT privilege on sequence
USAGE privilege on sequence schema

Restrictions

You cannot invoke CURRVAL in a SELECT statement, in the following contexts:

WHERE clause
GROUP BY clause
ORDER BY clause
DISTINCT clause
UNION
Subquery

You also cannot invoke CURRVAL to act on a sequence in:

UPDATE or DELETE subqueries
Views

Examples

See Creating and using named sequences.

4.2 - NEXTVAL

Returns the next value in a sequence.

Returns the next value in a sequence. Call NEXTVAL after creating a sequence to initialize the sequence with its default value. Thereafter, call NEXTVAL to increment the sequence value for ascending sequences, or decrement its value for descending sequences.

Syntax

NEXTVAL ('[[database.]schema.]sequence')

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
sequence: Identifies the target sequence.

Privileges

SELECT privilege on sequence
USAGE privilege on sequence schema

Restrictions

You cannot invoke NEXTVAL in a SELECT statement, in the following contexts:

WHERE clause
GROUP BY clause
ORDER BY clause
DISTINCT clause
UNION
Subquery

You also cannot invoke NEXTVAL to act on a sequence in:

UPDATE or DELETE subqueries
Views

You can use subqueries to work around some of these restrictions. For example, to use sequences with a DISTINCT clause:

=> SELECT t.col1, shift_allocation_seq.NEXTVAL FROM (
     SELECT DISTINCT col1 FROM av_temp1) t;

Examples

See Creating and using named sequences.

5 - String functions

String functions perform conversion, extraction, or manipulation operations on strings, or return information about strings.

This section describes functions and operators for examining and manipulating string values. Strings in this context include values of the types CHAR, VARCHAR, BINARY, and VARBINARY.

Unless otherwise noted, all of the functions listed in this section work on all four data types. As opposed to some other SQL implementations, Vertica keeps CHAR strings unpadded internally, padding them only on final output. So converting a CHAR(3) 'ab' to VARCHAR(5) results in a VARCHAR of length 2, not one with length 3 including a trailing space.

Some of the functions described here also work on data of non-string types by converting that data to a string representation first. Some functions work only on character strings, while others work only on binary strings. Many work for both. BINARY and VARBINARY functions ignore multibyte UTF-8 character boundaries.

Non-binary character string functions handle normalized multibyte UTF-8 characters, as specified by the Unicode Consortium. Unless otherwise specified, those character string functions for which it matters can optionally specify whether VARCHAR arguments should be interpreted as octet (byte) sequences, or as (locale-aware) sequences of UTF-8 characters. This is accomplished by adding "USING OCTETS" or "USING CHARACTERS" (default) as a parameter to the function.

Some character string functions are stable because in general UTF-8 case-conversion, searching and sorting can be locale dependent. Thus, LOWER is stable, while LOWERB is immutable. The USING OCTETS clause converts these functions into their "B" forms, so they become immutable. If the locale is set to collation=binary, which is the default, all string functions—except CHAR_LENGTH/CHARACTER_LENGTH, LENGTH, SUBSTR, and OVERLAY—are converted to their "B" forms and so are immutable.

BINARY implicitly converts to VARBINARY, so functions that take VARBINARY arguments work with BINARY.

For other functions that operate on strings (but not VARBINARY), see Regular expression functions.

5.1 - ASCII

Converts the first character of a VARCHAR datatype to an INTEGER.

Converts the first character of a VARCHAR datatype to an INTEGER. This function is the opposite of the CHR function.

ASCII operates on UTF-8 characters and single-byte ASCII characters. It returns the same results for the ASCII subset of UTF-8.

Behavior type

Immutable

Syntax

ASCII ( expression )

Arguments

expression: VARCHAR (string) to convert.

Examples

This example returns employee last names that begin with L. The ASCII equivalent of L is 76:

=> SELECT employee_last_name FROM employee_dimension
      WHERE ASCII(SUBSTR(employee_last_name, 1, 1)) = 76
       LIMIT 5;
 employee_last_name
--------------------
 Lewis
 Lewis
 Lampert
 Lampert
 Li
(5 rows)

5.2 - BIT_LENGTH

Returns the length of the string expression in bits (bytes * 8) as an INTEGER.

Returns the length of the string expression in bits (bytes * 8) as an INTEGER. BIT_LENGTH applies to the contents of VARCHAR and VARBINARY fields.

Behavior type

Immutable

Syntax

BIT_LENGTH ( expression )

Arguments

expression: (CHAR or VARCHAR or BINARY or VARBINARY) is the string to convert.

Examples

Expression	Result
`SELECT BIT_LENGTH('abc'::varbinary);`	`24`
`SELECT BIT_LENGTH('abc'::binary);`	`8`
`SELECT BIT_LENGTH(''::varbinary);`	`0`
`SELECT BIT_LENGTH(''::binary);`	`8`
`SELECT BIT_LENGTH(null::varbinary);`
`SELECT BIT_LENGTH(null::binary);`
`SELECT BIT_LENGTH(VARCHAR 'abc');`	`24`
`SELECT BIT_LENGTH(CHAR 'abc');`	`24`
`SELECT BIT_LENGTH(CHAR(6) 'abc');`	`48`
`SELECT BIT_LENGTH(VARCHAR(6) 'abc');`	`24`
`SELECT BIT_LENGTH(BINARY(6) 'abc');`	`48`
`SELECT BIT_LENGTH(BINARY 'abc');`	`24`
`SELECT BIT_LENGTH(VARBINARY 'abc');`	`24`
`SELECT BIT_LENGTH(VARBINARY(6) 'abc');`	`24`

5.3 - BITCOUNT

Returns the number of one-bits (sometimes referred to as set-bits) in the given VARBINARY value.

Returns the number of one-bits (sometimes referred to as set-bits) in the given VARBINARY value. This is also referred to as the population count.

Behavior type

Immutable

Syntax

BITCOUNT ( expression )

Arguments

expression: (BINARY or VARBINARY) is the string to return.

Examples

=> SELECT BITCOUNT(HEX_TO_BINARY('0x10'));
 BITCOUNT
----------
        1
(1 row)
=> SELECT BITCOUNT(HEX_TO_BINARY('0xF0'));
 BITCOUNT
----------
        4
(1 row)
=> SELECT BITCOUNT(HEX_TO_BINARY('0xAB'));
 BITCOUNT
----------
        5
(1 row)

5.4 - BITSTRING_TO_BINARY

Translates the given VARCHAR bitstring representation into a VARBINARY value.

Translates the given VARCHAR bitstring representation into a VARBINARY value. This function is the inverse of TO_BITSTRING.

Behavior type

Immutable

Syntax

BITSTRING_TO_BINARY ( expression )

Arguments

expression: The VARCHAR string to process.

Examples

If there are an odd number of characters in the hex value, the first character is treated as the low nibble of the first (furthest to the left) byte.

=> SELECT BITSTRING_TO_BINARY('0110000101100010');
 BITSTRING_TO_BINARY
---------------------
 ab
(1 row)

5.5 - BTRIM

Removes the longest string consisting only of specified characters from the start and end of a string.

Behavior type

Immutable

Syntax

BTRIM ( expression [ , characters-to-remove ] )

Arguments

expression: (CHAR or VARCHAR) is the string to modify
characters-to-remove: (CHAR or VARCHAR) specifies the characters to remove. The default is the space character.

Examples

=> SELECT BTRIM('xyxtrimyyx', 'xy');
 BTRIM
-------
 trim
(1 row)

5.6 - CHARACTER_LENGTH

The CHARACTER_LENGTH() function:.

The CHARACTER_LENGTH() function:

Returns the string length in UTF-8 characters for CHAR and VARCHAR columns
Returns the string length in bytes (octets) for BINARY and VARBINARY columns
Strips the padding from CHAR expressions but not from VARCHAR expressions
Is identical to LENGTH() for CHAR and VARCHAR. For binary types, CHARACTER_LENGTH() is identical to OCTET_LENGTH().

Behavior type

Immutable if USING OCTETS, stable otherwise.

Syntax

[ CHAR_LENGTH | CHARACTER_LENGTH ] ( expression ... [ USING { CHARACTERS | OCTETS } ] )

Arguments

expression: (CHAR or VARCHAR) is the string to measure
USING CHARACTERS | OCTETS: Determines whether the character length is expressed in characters (the default) or octets.

Examples

=> SELECT CHAR_LENGTH('1234  '::CHAR(10) USING OCTETS);
 octet_length
--------------
            4
(1 row)

=> SELECT CHAR_LENGTH('1234  '::VARCHAR(10));
 char_length
-------------
           6
(1 row)

=> SELECT CHAR_LENGTH(NULL::CHAR(10)) IS NULL;
 ?column?
----------
 t
(1 row)

5.7 - CHR

Converts the first character of an INTEGER datatype to a VARCHAR.

Behavior type

Immutable

Syntax

CHR ( expression )

Arguments

expression: (INTEGER) is the string to convert and is masked to a single character.

Notes

CHR is the opposite of the ASCII function.
CHR operates on UTF-8 characters, not only on single-byte ASCII characters. It continues to get the same results for the ASCII subset of UTF-8.

Examples

This example returns the VARCHAR datatype of the CHR expressions 65 and 97 from the employee table:


=> SELECT CHR(65), CHR(97) FROM employee;
 CHR | CHR
-----+-----
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
 A   | a
(12 rows)

5.8 - COLLATION

Applies a collation to two or more strings.

Applies a collation to two or more strings. Use COLLATION with ORDER BY, GROUP BY, and equality clauses.

Syntax

COLLATION ( 'expression' [ , 'locale_or_collation_name' ] )

Arguments

'expression'

Any expression that evaluates to a column name or to two or more values of type CHAR or VARCHAR.

'locale_or_collation_name'

The ICU (International Components for Unicode) locale or collation name to use when collating the string. If you omit this parameter, COLLATION uses the collation associated with the session locale.

To determine the current session locale, enter the vsql meta-command \locale:

=> \locale
en_US@collation=binary

To set the locale and collation, use \locale as follows:

=> \locale en_US@collation=binary
INFO 2567:  Canonical locale: 'en_US'
Standard collation: 'LEN_KBINARY'
English (United States)

Locales

The locale used for COLLATION can be one of the following:

The default locale
A session locale
A locale that you specify when you call COLLATION. If you specify the locale, Vertica applies the collation associated with that locale to the data. COLLATION does not modify the collation for any other columns in the table.

For a list of valid ICU locales, go to Locale Explorer (ICU).

Binary and non-binary collations

The Vertica default locale is en_US@collation=binary, which uses binary collation. Binary collation compares binary representations of strings. Binary collation is fast, but it can result in a sort order where K precedes c because the binary representation of K is lower than c.

For non-binary collation, Vertica transforms the data according to the rules of the locale or the specified collation, and then applies the sorting rules. Suppose the locale collation is non-binary and you request a GROUP BY on string data. In this case,Vertica calls COLLATION, whether or not you specify the function in your query.

For information about collation naming, see Collator Naming Scheme.

Examples

Collating GROUP BY results

The following examples are based on a Premium_Customer table that contains the following data:

=> SELECT * FROM Premium_Customer;
 ID | LName  | FName
----+--------+---------
  1 | Mc Coy | Bob
  2 | Mc Coy | Janice
  3 | McCoy  | Jody
  4 | McCoy  | Peter
  5 | McCoy  | Brendon
  6 | Mccoy  | Cameron
  7 | Mccoy  | Lisa

The first statement shows how COLLATION applies the collation for the EN_US locale to the LName column for the locale EN_US. Vertica sorts the GROUP BY output as follows:

Last names with spaces
Last names where "coy" starts with a lowercase letter
Last names where "Coy" starts with an uppercase letter

=> SELECT * FROM Premium_Customer ORDER BY COLLATION(LName, 'EN_US'), FName;
 ID | LName  | FName
----+--------+---------
  1 | Mc Coy | Bob
  2 | Mc Coy | Janice
  6 | Mccoy  | Cameron
  7 | Mccoy  | Lisa
  5 | McCoy  | Brendon
  3 | McCoy  | Jody
  4 | McCoy  | Peter

The next statement shows how COLLATION collates the LName column for the locale LEN_AS:

LEN indicates the language (L) is English (EN).
AS (Alternate Shifted) instructs COLLATION that lowercase letters come before uppercase (shifted) letters.

In the results, the last names in which "coy" starts with a lowercase letter precede the last names where "Coy" starts with an uppercase letter.

=> SELECT * FROM Premium_Customer ORDER BY COLLATION(LName, 'LEN_AS'), FName;
 ID | LName  | FName
----+--------+---------
  6 | Mccoy  | Cameron
  7 | Mccoy  | Lisa
  1 | Mc Coy | Bob
  5 | McCoy  | Brendon
  2 | Mc Coy | Janice
  3 | McCoy  | Jody
  4 | McCoy  | Peter

Comparing strings with an equality clause

In the following query, COLLATION removes spaces and punctuation when comparing two strings in English. It then determines whether the two strings still have the same value after the punctuation has been removed:

=> SELECT COLLATION ('U.S.A', 'LEN_AS') = COLLATION('USA', 'LEN_AS');
?column?
----------
 t

Sorting strings in non-english languages

The following table contains data that uses the German character eszett, ß:

=> SELECT * FROM t1;
     a      | b | c
------------+---+----
 ßstringß   | 1 | 10
 SSstringSS | 2 | 20
 random1    | 3 | 30
 random1    | 4 | 40
 random2    | 5 | 50

When you specify the collation LDE_S1:

LDE indicates the language (L) is German (DE).
S1 indicates the strength (S) of 1 (primary). This value indicates that the collation does not need to consider accents and case.

The query returns the data in the following order:

=> SELECT a FROM t1 ORDER BY COLLATION(a, 'LDE_S1'));
     a
------------
 random1
 random1
 random2
 SSstringSS
 ßstringß

5.9 - CONCAT

Concatenates two strings and returns a varchar data type.

Concatenates two strings and returns a varchar data type. If either argument is null, concat returns null.

Syntax

CONCAT ('string-expression1, string-expression2)

Behavior type

Immutable

Arguments

string-expression1, string-expression2: The values to concatenate, any data type that can be cast to a string value.

Examples

The following examples use a sample table named alphabet with two varchar columns:

=> CREATE TABLE alphabet (letter1 varchar(2), letter2 varchar(2));
CREATE TABLE
=> COPY alphabet FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> A|B
>> C|D
>> \.
=> SELECT * FROM alphabet;
 letter1 | letter2
---------+---------
 C       | D
 A       | B
(2 rows)

Concatenate the contents of the first column with a character string:

=> SELECT CONCAT(letter1, ' is a letter') FROM alphabet;
    CONCAT
---------------
 A is a letter
 C is a letter
(2 rows)

Concatenate the output of two nested CONCAT functions:

=> SELECT CONCAT(CONCAT(letter1, ' and '), CONCAT(letter2, ' are both letters')) FROM alphabet;
          CONCAT
--------------------------
 C and D are both letters
 A and B are both letters
(2 rows)

Concatenate a date and string:

=> SELECT current_date today;
   today
------------
 2021-12-10
(1 row)

=> SELECT CONCAT('2021-12-31'::date - current_date, ' days until end of year 2021');
             CONCAT
--------------------------------
 21 days until end of year 2021
(1 row)

5.10 - DECODE

Compares expression to each search value one by one.

Compares *expression *to each search value one by one. If *expression *is equal to a search, the function returns the corresponding result. If no match is found, the function returns default. If default is omitted, the function returns null.

DECODE is similar to the IF-THEN-ELSE and CASE expressions:

CASE expression
[WHEN search THEN result]
[WHEN search THEN result]
...
[ELSE default];

The arguments can have any data type supported by Vertica. The result types of individual results are promoted to the least common type that can be used to represent all of them. This leads to a character string type, an exact numeric type, an approximate numeric type, or a DATETIME type, where all the various result arguments must be of the same type grouping.

Behavior type

Immutable

Syntax

DECODE ( expression, search, result [ , search, result ]...[, default ] )

Arguments

expression: The value to compare.
search: The value compared against expression.
result: The value returned, if *expression *is equal to search.
default: Optional. If no matches are found, DECODE returns default. If default is omitted, then DECODE returns NULL (if no matches are found).

Examples

The following example converts numeric values in the weight column from the product_dimension table to descriptive values in the output.

=> SELECT product_description, DECODE(weight,
      2, 'Light',
     50, 'Medium',
     71, 'Heavy',
     99, 'Call for help',
         'N/A')
  FROM product_dimension
  WHERE category_description = 'Food'
  AND department_description = 'Canned Goods'
  AND sku_number BETWEEN 'SKU-#49750' AND 'SKU-#49999'
  LIMIT 15;
        product_description        |     case
-----------------------------------+---------------
 Brand #499 canned corn            | N/A
 Brand #49900 fruit cocktail       | Medium
 Brand #49837 canned tomatoes      | Heavy
 Brand #49782 canned peaches       | N/A
 Brand #49805 chicken noodle soup  | N/A
 Brand #49944 canned chicken broth | N/A
 Brand #49819 canned chili         | N/A
 Brand #49848 baked beans          | N/A
 Brand #49989 minestrone soup      | N/A
 Brand #49778 canned peaches       | N/A
 Brand #49770 canned peaches       | N/A
 Brand #4977 fruit cocktail        | N/A
 Brand #49933 canned olives        | N/A
 Brand #49750 canned olives        | Call for help
 Brand #49777 canned tomatoes      | N/A
(15 rows)

5.11 - EDIT_DISTANCE

Calculates and returns the Levenshtein distance between two strings.

Calculates and returns the Levenshtein distance between two strings. The return value indicates the minimum number of single-character edits—insertions, deletions, or substitutions—that are required to change one string into the other. Compare to Jaro distance and Jaro-Winkler distance.

Behavior type

Immutable

Syntax

EDIT_DISTANCE ( string-expression1, string-expression2 )

Arguments

string-expression1, string-expression2: The two VARCHAR expressions to compare.

Examples

The Levenshtein distance between kitten and knitting is 3:

=> SELECT EDIT_DISTANCE ('kitten', 'knitting');
 EDIT_DISTANCE
---------------
             3
(1 row)

EDIT_DISTANCE calculates that no fewer than three changes are required to transform kitten to knitting:

kitten → knitten (insert n after k)
knitten → knittin (substitute i for e)
knittin → knitting (append g)

5.12 - GREATEST

Returns the largest value in a list of expressions of any data type.

Returns the largest value in a list of expressions of any data type. All data types in the list must be the same or compatible. A NULL value in any one of the expressions returns NULL. Results can vary, depending on the locale's collation setting.

Behavior type

Stable

Syntax

GREATEST ( { * | expression[,...] } )

Arguments

* | expression[,...]

The expressions to evaluate, one of the following:

* (asterisk)

Evaluates all columns in the queried table.
expression

An expression of any data type. Functions that are included in expression must be deterministic.

Examples

GREATEST returns 10 as the largest value in the list:

=> SELECT GREATEST(7,5,10);
 GREATEST
----------
       10
(1 row)

If you put quotes around the integer expressions, GREATEST compares the values as strings and returns '7' as the greatest value:

=> SELECT GREATEST('7', '5', '10');
 GREATEST
----------
 7
(1 row)

The next example returns FLOAT 1.5 as the greatest because the integer is implicitly cast to float:

=> SELECT GREATEST(1, 1.5);
 GREATEST
----------
      1.5
(1 row)

GREATEST queries all columns in a view based on the VMart table product_dimension, and returns the largest value in each row:

=> CREATE VIEW query1 AS SELECT shelf_width, shelf_height, shelf_depth FROM product_dimension;
CREATE VIEW
=> SELECT shelf_width, shelf_height, shelf_depth, greatest(*) FROM query1 WHERE shelf_width = 1;
 shelf_width | shelf_height | shelf_depth | greatest
-------------+--------------+-------------+----------
           1 |            3 |           1 |        3
           1 |            3 |           3 |        3
           1 |            5 |           4 |        5
           1 |            2 |           2 |        2
           1 |            1 |           3 |        3
           1 |            2 |           2 |        2
           1 |            2 |           3 |        3
           1 |            1 |           5 |        5
           1 |            1 |           4 |        4
           1 |            5 |           3 |        5
           1 |            4 |           2 |        4
           1 |            4 |           5 |        5
           1 |            5 |           3 |        5
           1 |            2 |           5 |        5
           1 |            4 |           2 |        4
           1 |            4 |           4 |        4
           1 |            1 |           2 |        2
           1 |            4 |           3 |        4
...

5.13 - GREATESTB

Returns the largest value in a list of expressions of any data type, using binary ordering.

Returns the largest value in a list of expressions of any data type, using binary ordering. All data types in the list must be the same or compatible. A NULL value in any one of the expressions returns NULL. Results can vary, depending on the locale's collation setting.

Behavior type

Immutable

Syntax

GREATEST ( { * | expression[,...] } )

Arguments

* | expression[,...]

The expressions to evaluate, one of the following:

* (asterisk)

Evaluates all columns in the queried table.
expression

An expression of any data type. Functions that are included in expression must be deterministic.

Examples

The following command selects straße as the greatest in the series of inputs:

=> SELECT GREATESTB('straße', 'strasse');
 GREATESTB
-----------
 straße
(1 row)

GREATESTB returns 10 as the largest value in the list:

=> SELECT GREATESTB(7,5,10);
 GREATESTB
-----------
        10
(1 row)

If you put quotes around the integer expressions, GREATESTB compares the values as strings and returns '7' as the greatest value:

=> SELECT GREATESTB('7', '5', '10');
 GREATESTB
-----------
 7
(1 row)

The next example returns FLOAT 1.5 as the greatest because the integer is implicitly cast to float:

=> SELECT GREATESTB(1, 1.5);
 GREATESTB
-----------
       1.5
(1 row)

GREATESTB queries all columns in a view based on the VMart table product_dimension, and returns the largest value in each row:

=> CREATE VIEW query1 AS SELECT shelf_width, shelf_height, shelf_depth FROM product_dimension;
CREATE VIEW
=> SELECT shelf_width, shelf_height, shelf_depth, greatestb(*) FROM query1 WHERE shelf_width = 1;
 shelf_width | shelf_height | shelf_depth | greatestb
-------------+--------------+-------------+-----------
           1 |            3 |           1 |         3
           1 |            3 |           3 |         3
           1 |            5 |           4 |         5
           1 |            2 |           2 |         2
           1 |            1 |           3 |         3
           1 |            2 |           2 |         2
           1 |            2 |           3 |         3
           1 |            1 |           5 |         5
           1 |            1 |           4 |         4
           1 |            5 |           3 |         5
           1 |            4 |           2 |         4
           1 |            4 |           5 |         5
           1 |            5 |           3 |         5
           1 |            2 |           5 |         5
           1 |            4 |           2 |         4
...

5.14 - HEX_TO_BINARY

Translates the given VARCHAR hexadecimal representation into a VARBINARY value.

Behavior type

Immutable

Syntax

HEX_TO_BINARY ( [ 0x ] expression )

Arguments

expression: (BINARY or VARBINARY) String to translate.
0x: Optional prefix.

Notes

VARBINARY HEX_TO_BINARY(VARCHAR) converts data from character type in hexadecimal format to binary type. This function is the inverse of TO_HEX.

HEX_TO_BINARY(TO_HEX(x)) = x)
TO_HEX(HEX_TO_BINARY(x)) = x)

If there are an odd number of characters in the hexadecimal value, the first character is treated as the low nibble of the first (furthest to the left) byte.

Examples

If the given string begins with "0x" the prefix is ignored. For example:

=> SELECT HEX_TO_BINARY('0x6162') AS hex1, HEX_TO_BINARY('6162') AS hex2;
 hex1 | hex2
------+------
 ab   | ab
(1 row)

If an invalid hex value is given, Vertica returns an “invalid binary representation" error; for example:

=> SELECT HEX_TO_BINARY('0xffgf');
ERROR:  invalid hex string "0xffgf"

5.15 - HEX_TO_INTEGER

Translates the given VARCHAR hexadecimal representation into an INTEGER value.

Vertica completes this conversion as follows:

Adds the 0x prefix if it is not specified in the input
Casts the VARCHAR string to a NUMERIC
Casts the NUMERIC to an INTEGER

Behavior type

Immutable

Syntax

HEX_TO_INTEGER ( [ 0x ] expression )

Arguments

expression: VARCHAR is the string to translate.
0x: Is the optional prefix.

Examples

You can enter the string with or without the Ox prefix. For example:

=> SELECT HEX_TO_INTEGER ('0aedc')
         AS hex1,HEX_TO_INTEGER ('aedc') AS hex2;
 hex1  | hex2
-------+-------
 44764 | 44764
(1 row)

If you pass the function an invalid hex value, Vertica returns an invalid input syntax error; for example:

=> SELECT HEX_TO_INTEGER ('0xffgf');
ERROR 3691:  Invalid input syntax for numeric: "0xffgf"

5.16 - INITCAP

Capitalizes first letter of each alphanumeric word and puts the rest in lowercase.

Behavior type

Immutable

Syntax

INITCAP ( expression )

Arguments

expression: (VARCHAR) is the string to format.

Notes

Depends on collation setting of the locale.
INITCAP is restricted to 32750 octet inputs, since it is possible for the UTF-8 representation of result to double in size.

Examples

Expression	Result
`SELECT INITCAP('high speed database');`	`High Speed Database`
`SELECT INITCAP('LINUX TUTORIAL');`	`Linux Tutorial`
`SELECT INITCAP('abc DEF 123aVC 124Btd,lAsT');`	`Abc Def 123Avc 124Btd,Last`
`SELECT INITCAP('');`
`SELECT INITCAP(null);`

5.17 - INITCAPB

Capitalizes first letter of each alphanumeric word and puts the rest in lowercase.

Capitalizes first letter of each alphanumeric word and puts the rest in lowercase. Multibyte characters are not converted and are skipped.

Behavior type

Immutable

Syntax

INITCAPB ( expression )

Arguments

expression: (VARCHAR) is the string to format.

Notes

Depends on collation setting of the locale.

Examples

Expression	Result
`SELECT INITCAPB('étudiant');`	`éTudiant`
`SELECT INITCAPB('high speed database');`	`High Speed Database`
`SELECT INITCAPB('LINUX TUTORIAL');`	`Linux Tutorial`
`SELECT INITCAPB('abc DEF 123aVC 124Btd,lAsT');`	`Abc Def 123Avc 124Btd,Last`
`SELECT INITCAPB('');`
`SELECT INITCAPB(null);`

5.18 - INSERT

Inserts a character string into a specified location in another character string.

Syntax

INSERT( 'string1', n, m, 'string2' )

Behavior type

Immutable

Arguments

string1: (VARCHAR) Is the string in which to insert the new string.
n: A character of type INTEGER that represents the starting point for the insertion within* string1*. You specify the number of characters from the first character in string1 as the starting point for the insertion. For example, to insert characters before "c", in the string "abcdef," enter 3.
m: A character of type INTEGER that represents the number of characters in*string1(if any) *that should be replaced by the insertion. For example,if you want the insertion to replace the letters "cd" in the string "abcdef, " enter 2.
string2: (VARCHAR) Is the string to be inserted.

Examples

The following example changes the string Warehouse to Storehouse using the INSERT function:

=> SELECT INSERT ('Warehouse',1,3,'Stor');
   INSERT
------------
 Storehouse
(1 row)

5.19 - INSTR

Searches string for substring and returns an integer indicating the position of the character in string that is the first character of this occurrence.

Searches *string *for *substring *and returns an integer indicating the position of the character in *string *that is the first character of this occurrence. The return value is based on the character position of the identified character.

Behavior type

Immutable

Syntax

INSTR ( string , substring [, position [, occurrence ] ] )

Arguments

string: (CHAR or VARCHAR, or BINARY or VARBINARY) Text expression to search.
substring: (CHAR or VARCHAR, or BINARY or VARBINARY) String to search for.
position: Nonzero integer indicating the character of string where Vertica begins the search. If position is negative, then Vertica counts backward from the end of string and then searches backward from the resulting position. The first character of string occupies the default position 1, and position cannot be 0.
occurrence: Integer indicating which occurrence of string Vertica searches. The value of occurrence must be positive (greater than 0), and the default is 1.

Notes

Both position and occurrence must be of types that can resolve to an integer. The default values of both parameters are 1, meaning Vertica begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters.

If the search is unsuccessful (that is, if substring does not appear *occurrence *times after the position character of string, the return value is 0.

Examples

The first example searches forward in string ‘abc’ for substring ‘b’. The search returns the position in ‘abc’ where ‘b’ occurs, or position 2. Because no position parameters are given, the default search starts at ‘a’, position 1.

=> SELECT INSTR('abc', 'b');
 INSTR
-------
     2
(1 row)

The following three examples use character position to search backward to find the position of a substring.

Note

Although it might seem intuitive that the function returns a negative integer, the position of n occurrence is read left to right in the sting, even though the search happens in reverse (from the end—or right side—of the string).

In the first example, the function counts backward one character from the end of the string, starting with character ‘c’. The function then searches backward for the first occurrence of ‘a’, which it finds it in the first position in the search string.

=> SELECT INSTR('abc', 'a', -1);
 INSTR
-------
     1
(1 row)

In the second example, the function counts backward one byte from the end of the string, starting with character ‘c’. The function then searches backward for the first occurrence of ‘a’, which it finds it in the first position in the search string.

=> SELECT INSTR(VARBINARY 'abc', VARBINARY 'a', -1);
 INSTR
-------
     1
(1 row)

In the third example, the function counts backward one character from the end of the string, starting with character ‘b’, and searches backward for substring ‘bc’, which it finds in the second position of the search string.

=> SELECT INSTR('abcb', 'bc', -1);
 INSTR
-------
     2
(1 row)

In the fourth example, the function counts backward one character from the end of the string, starting with character ‘b’, and searches backward for substring ‘bcef’, which it does not find. The result is 0.

=> SELECT INSTR('abcb', 'bcef', -1);
INSTR
-------
     0
(1 row)

In the fifth example, the function counts backward one byte from the end of the string, starting with character ‘b’, and searches backward for substring ‘bcef’, which it does not find. The result is 0.

=> SELECT INSTR(VARBINARY 'abcb', VARBINARY 'bcef', -1);
INSTR
-------
     0
(1 row)

Multibyte characters are treated as a single character:

=> SELECT INSTR('aébc', 'b');
 INSTR
-------
     3
(1 row)

Use INSTRB to treat multibyte characters as binary:

=> SELECT INSTRB('aébc', 'b');
  INSTRB
--------
      4
(1 row)

5.20 - INSTRB

Searches string for substring and returns an integer indicating the octet position within string that is the first occurrence.

Searches string for substring and returns an integer indicating the octet position within string that is the first occurrence. The return value is based on the octet position of the identified byte.

Behavior type

Immutable

Syntax

INSTRB ( string , substring [, position [, occurrence ] ] )

Arguments

string: Is the text expression to search.
substring: Is the string to search for.
position: Is a nonzero integer indicating the character of string where Vertica begins the search. If position is negative, then Vertica counts backward from the end of string and then searches backward from the resulting position. The first byte of string occupies the default position 1, and position cannot be 0.
occurrence: Is an integer indicating which occurrence of string Vertica searches. The value of occurrence must be positive (greater than 0), and the default is 1.

Notes

Both position and occurrence must be of types that can resolve to an integer. The default values of both parameters are 1, meaning Vertica begins searching at the first byte of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in octets.

If the search is unsuccessful (that is, if substring does not appear *occurrence *times after the *position *character of *string, *then the return value is 0.

Examples

=> SELECT INSTRB('straße', 'ß');
 INSTRB
--------
      5
(1 row)

5.21 - ISUTF8

Tests whether a string is a valid UTF-8 string.

Tests whether a string is a valid UTF-8 string. Returns true if the string conforms to UTF-8 standards, and false otherwise. This function is useful to test strings for UTF-8 compliance before passing them to one of the regular expression functions, such as REGEXP_LIKE, which expect UTF-8 characters by default.

ISUTF8 checks for invalid UTF8 byte sequences, according to UTF-8 rules:

invalid bytes
an unexpected continuation byte
a start byte not followed by enough continuation bytes
an Overload Encoding

The presence of an invalid UTF-8 byte sequence results in a return value of false.

To coerce a string to UTF-8, use MAKEUTF8.

Syntax

ISUTF8( string );

Arguments

string: The string to test for UTF-8 compliance.

Examples

=> SELECT ISUTF8(E'\xC2\xBF'); -- UTF-8 INVERTED QUESTION MARK ISUTF8
--------
 t
(1 row)

=> SELECT ISUTF8(E'\xC2\xC0'); -- UNDEFINED UTF-8 CHARACTER
 ISUTF8
--------
 f
(1 row)

5.22 - JARO_DISTANCE

Calculates and returns the Jaro similarity, an edit distance between two sequences.

Calculates and returns the Jaro similarity, an edit distance between two sequences. It is useful for queries designed for short strings, such as finding similar names. Also see Jaro-Winkler distance, which adds a prefix scale favoring strings that match in the beginning, and edit distance, which returns the Levenshtein distance between two strings.

Behavior type

Immutable

Syntax

JARO_DISTANCE (string-expression1, string-expression2)

Arguments

string-expression1, string-expression2: The two VARCHAR expressions to compare. Neither can be NULL.

Example

Return only the names with a Jaro distance from 'rode' that is greater than 0.6:

=> SELECT name FROM names WHERE JARO_DISTANCE('rode', name) > 0.6;
name
---------
fred
frieda
rodgers
rogers
(4 rows)

5.23 - JARO_WINKLER_DISTANCE

Calculates and returns the Jaro-Winkler similarity, an edit distance between two sequences.

Calculates and returns the Jaro-Winkler similarity, an edit distance between two sequences. It is useful for queries designed for short strings, such as finding similar names. It is a variant of the Jaro distance metric, to which it adds a prefix scale giving more favorable ratings for strings that match from the beginning. See also edit distance, which returns the Levenshtein distance between two strings.

Behavior type

Immutable

Syntax

JARO_WINKLER_DISTANCE (string-expression1 , string-expression2 [ USING PARAMETERS prefix_scale=scale, prefix_length=length])

Arguments

string-expression1, string-expression2: The two VARCHAR expressions to compare. Neither can be NULL.

Parameters

scale: A FLOAT specifying the scale value by which to weight the importance of matching prefixes. Optional.
default = 0.1
length: An non-negative INT representing the maximum matching prefix length. Optional.
default = 4

Examples

Return only the names with a Jaro-Winkler distance from 'rode' that is greater than 0.6:

=> SELECT name FROM names WHERE JARO_WINKLER_DISTANCE('rode', name) > 0.6;
name
---------
fred
frieda
rodgers
rogers
(4 rows)

The Jaro-Winkler distance between 'help' and 'hello' given a prefix_scale of 0.1 and prefix_length of 0 is 0.783333333333333:

=> select JARO_WINKLER_DISTANCE('help', 'hello' USING PARAMETERS prefix_scale=0.1, prefix_length=0);
jaro_winkler_distance
-----------------------
0.783333333333333
(1 row)

5.24 - LEAST

Returns the smallest value in a list of expressions of any data type.

Returns the smallest value in a list of expressions of any data type. All data types in the list must be the same or compatible. A NULL value in any one of the expressions returns NULL. Results can vary, depending on the locale's collation setting.

Behavior type

Stable

Syntax

LEAST ( { * | expression[,...] } )

Arguments

* | expression[,...]

The expressions to evaluate, one of the following:

* (asterisk)

Evaluates all columns in the queried table.
expression

An expression of any data type. Functions that are included in expression must be deterministic.

Examples

LEASTB returns 5 as the smallest value in the list:

=> SELECT LEASTB(7, 5, 10);
 LEASTB
--------
      5
(1 row)

If you put quotes around the integer expressions, LEASTB compares the values as strings and returns '10' as the smallest value:

=> SELECT LEASTB('7', '5', '10');
 LEASTB
--------
 10
(1 row)

LEAST returns 1.5, as INTEGER 2 is implicitly cast to FLOAT:

=> SELECT LEAST(2, 1.5);
 LEAST
-------
   1.5
(1 row)

LEAST queries all columns in a view based on the VMart table product_dimension, and returns the smallest value in each row:

=> CREATE VIEW query1 AS SELECT shelf_width, shelf_height, shelf_depth FROM product_dimension;
CREATE VIEW
=> SELECT shelf_height, shelf_width, shelf_depth, least(*) FROM query1 WHERE shelf_height = 5;
 shelf_height | shelf_width | shelf_depth | least
--------------+-------------+-------------+-------
            5 |           3 |           4 |     3
            5 |           4 |           3 |     3
            5 |           1 |           4 |     1
            5 |           4 |           1 |     1
            5 |           2 |           4 |     2
            5 |           2 |           3 |     2
            5 |           1 |           3 |     1
            5 |           1 |           3 |     1
            5 |           5 |           1 |     1
            5 |           2 |           4 |     2
            5 |           4 |           5 |     4
            5 |           2 |           4 |     2
            5 |           4 |           4 |     4
            5 |           3 |           4 |     3
...

5.25 - LEASTB

Returns the smallest value in a list of expressions of any data type, using binary ordering.

Returns the smallest value in a list of expressions of any data type, using binary ordering. All data types in the list must be the same or compatible. A NULL value in any one of the expressions returns NULL. Results can vary, depending on the locale's collation setting.

Behavior type

Immutable

Syntax

LEASTB ( { * | expression[,...] } )

Arguments

* | expression[,...]

The expressions to evaluate, one of the following:

* (asterisk)

Evaluates all columns in the queried table.
expression

An expression of any data type. Functions that are included in expression must be deterministic.

Examples

The following command selects strasse as the smallest value in the list:

=> SELECT LEASTB('straße', 'strasse');
 LEASTB
---------
 strasse
(1 row)

LEASTB returns 5 as the smallest value in the list:

=> SELECT LEAST(7, 5, 10);
 LEAST
-------
     5
(1 row)

If you put quotes around the integer expressions, LEAST compares the values as strings and returns '10' as the smallest value:

=> SELECT LEASTB('7', '5', '10');
 LEAST
-------
 10
(1 row)

The next example returns 1.5, as INTEGER 2 is implicitly cast to FLOAT:

=> SELECT LEASTB(2, 1.5);
 LEASTB
--------
    1.5
(1 row)

LEASTB queries all columns in a view based on the VMart table product_dimension, and returns the smallest value in each row:

=> CREATE VIEW query1 AS SELECT shelf_width, shelf_height, shelf_depth FROM product_dimension;
CREATE VIEW
=> SELECT shelf_height, shelf_width, shelf_depth, leastb(*) FROM query1 WHERE shelf_height = 5;
 shelf_height | shelf_width | shelf_depth | leastb
--------------+-------------+-------------+--------
            5 |           3 |           4 |      3
            5 |           4 |           3 |      3
            5 |           1 |           4 |      1
            5 |           4 |           1 |      1
            5 |           2 |           4 |      2
            5 |           2 |           3 |      2
            5 |           1 |           3 |      1
            5 |           1 |           3 |      1
            5 |           5 |           1 |      1
            5 |           2 |           4 |      2
            5 |           4 |           5 |      4
            5 |           2 |           4 |      2
            5 |           4 |           4 |      4
            5 |           3 |           4 |      3
            5 |           5 |           4 |      4
            5 |           5 |           1 |      1
            5 |           3 |           1 |      1
...

5.26 - LEFT

Returns the specified characters from the left side of a string.

Behavior type

Immutable

Syntax

LEFT ( string-expr, length )

Arguments

string-expr: The string expression to return.
length: An integer value that specifies how many characters to return.

Examples

=> SELECT LEFT('vertica', 3);
 LEFT
------
 ver
(1 row)

 SELECT DISTINCT(
   LEFT (customer_name, 4)) FnameTruncated
   FROM customer_dimension ORDER BY FnameTruncated LIMIT 10;
 FnameTruncated
----------------
 Alex
 Amer
 Amy
 Anna
 Barb
 Ben
 Bett
 Bria
 Carl
 Crai
(10 rows)

5.27 - LENGTH

Returns the length of a string.

Returns the length of a string. The behavior of LENGTH varies according to the input data type:

CHAR and VARCHAR: Identical to CHARACTER_LENGTH, returns the string length in UTF-8 characters, .
CHAR: Strips padding.
BINARY and VARBINARY: Identical to OCTET_LENGTH, returns the string length in bytes (octets).

Behavior type

Immutable

Syntax

LENGTH ( expression )

Arguments

expression: String to evaluate, one of the following: CHAR, VARCHAR, BINARY or VARBINARY.

Examples

Statement	Returns
`SELECT LENGTH('1234 '::CHAR(10));`	`4`
`SELECT LENGTH('1234 '::VARCHAR(10));`	`6`
`SELECT LENGTH('1234 '::BINARY(10));`	`10`
`SELECT LENGTH('1234 '::VARBINARY(10));`	`6`
`SELECT LENGTH(NULL::CHAR(10)) IS NULL;`	`t`

5.28 - LOWER

Takes a string value and returns a VARCHAR value converted to lowercase.

Behavior type

stable

Syntax

LOWER ( expression )

Arguments

expression: CHAR or VARCHAR string to convert, where the string width is ≤ 65000 octets.

Important

In practice, expression should not exceed 32,500 octets. LOWER does not use the locale's collation setting—for example, collation=binary—to identify its encoding; rather, it treats the input argument as a UTF-8 encoded string. The UTF-8 representation of the input value might be double its original width. As a result, LOWER returns an error if the input value exceeds 32,500 octets.

Note also that if expression is a table column, LOWER calculates its size from the column's defined width, and not from the column data. If the column width is greater than VARCHAR(32500), Vertica returns an error.

Examples

=> SELECT LOWER('AbCdEfG');
  LOWER
---------
 abcdefg
(1 row)

=> SELECT LOWER('The Bat In The Hat');
       LOWER
--------------------
 the bat in the hat
(1 row)

=> SELECT LOWER('ÉTUDIANT');
  LOWER
----------
 étudiant
(1 row)

5.29 - LOWERB

Returns a character string with each ASCII character converted to lowercase.

Returns a character string with each ASCII character converted to lowercase. Multi-byte characters are skipped and not converted.

Behavior type

Immutable

Syntax

LOWERB ( expression )

Arguments

expression: CHAR or VARCHAR string to convert

Examples

In the following example, the multi-byte UTF-8 character É is not converted to lowercase:

=> SELECT LOWERB('ÉTUDIANT');
  LOWERB
----------
 Étudiant
(1 row)

=> SELECT LOWER('ÉTUDIANT');
  LOWER
----------
 étudiant
(1 row)

=> SELECT LOWERB('AbCdEfG');
 LOWERB
---------
 abcdefg
(1 row)

=> SELECT LOWERB('The Vertica Database');
        LOWERB
----------------------
 the vertica database
(1 row)

5.30 - LPAD

Returns a VARCHAR value representing a string of a specific length filled on the left with specific characters.

Behavior type

Immutable

Syntax

LPAD ( expression , length [ , fill ] )

Arguments

expression: (CHAR OR VARCHAR) specifies the string to fill
length: (INTEGER) specifies the number of characters to return
fill: (CHAR OR VARCHAR) specifies the repeating string of characters with which to fill the output string. The default is the space character.

Examples

=> SELECT LPAD('database', 15, 'xzy');
      LPAD
-----------------
 xzyxzyxdatabase
(1 row)

If the string is already longer than the specified length it is truncated on the right:

=> SELECT LPAD('establishment', 10, 'abc');
    LPAD
------------
 establishm
(1 row)

5.31 - LTRIM

Returns a VARCHAR value representing a string with leading blanks removed from the left side (beginning).

Behavior type

Immutable

Syntax

LTRIM ( expression [ , characters ] )

Arguments

expression: (CHAR or VARCHAR) is the string to trim
characters: (CHAR or VARCHAR) specifies the characters to remove from the left side of expression. The default is the space character.

Examples

=> SELECT LTRIM('zzzyyyyyyxxxxxxxxtrim', 'xyz');
 LTRIM
-------
 trim
(1 row)

5.32 - MAKEUTF8

Coerces a string to UTF-8 by removing or replacing non-UTF-8 characters.

MAKEUTF8 flags invalid UTF-8 characters byte by byte. For example, the byte sequence 0xE0 0x7F 0x80 is an invalid three-byte UTF-8 sequence, but the middle byte, 0x7F, is a valid one-byte UTF-8 character. In this example, 0x7F is preserved and the other two bytes are removed or replaced.

Syntax

MAKEUTF8( string-expression [USING PARAMETERS param=value] );

Arguments

string-expression: The string expression to evaluate for non-UTF-8 characters

Parameters

replacement_string

Specifies the VARCHAR(16) string that MAKEUTF8 uses to replace each non-UTF-8 character that it finds in string-expression. If this parameter is omitted, non-UTF-8 characters are removed. For example, the following SQL specifies to replace all non-UTF characters in the name column with the string ^:

=> SELECT MAKEUTF8(name USING PARAMETERS replacement_string='^') FROM people;

5.33 - MD5

Calculates the MD5 hash of string, returning the result as a VARCHAR string in hexadecimal.

Behavior type

Immutable

Syntax

MD5 ( string )

Arguments

string: Is the argument string.

Examples

=> SELECT MD5('123');
               MD5
----------------------------------
 202cb962ac59075b964b07152d234b70
(1 row)

=> SELECT MD5('Vertica'::bytea);
               MD5
----------------------------------
 fc45b815747d8236f9f6fdb9c2c3f676
(1 row)

5.34 - OCTET_LENGTH

Takes one argument as an input and returns the string length in octets for all string types.

Behavior type

Immutable

Syntax

OCTET_LENGTH ( expression )

Arguments

expression: (CHAR or VARCHAR or BINARY or VARBINARY) is the string to measure.

Notes

If the data type of expression is a CHAR, VARCHAR or VARBINARY, the result is the same as the actual length of expression in octets. For CHAR, the length does not include any trailing spaces.
If the data type of expression is BINARY, the result is the same as the fixed-length of expression.
If the value of expression is NULL, the result is NULL.

Examples

Expression	Result
`SELECT OCTET_LENGTH(CHAR(10) '1234 ');`	`4`
`SELECT OCTET_LENGTH(CHAR(10) '1234');`	`4`
`SELECT OCTET_LENGTH(CHAR(10) ' 1234');`	`6`
`SELECT OCTET_LENGTH(VARCHAR(10) '1234 ');`	`6`
`SELECT OCTET_LENGTH(VARCHAR(10) '1234 ');`	`5`
`SELECT OCTET_LENGTH(VARCHAR(10) '1234');`	`4`
`SELECT OCTET_LENGTH(VARCHAR(10) ' 1234');`	`7`
`SELECT OCTET_LENGTH('abc'::VARBINARY);`	`3`
`SELECT OCTET_LENGTH(VARBINARY 'abc');`	`3`
`SELECT OCTET_LENGTH(VARBINARY 'abc ');`	`5`
`SELECT OCTET_LENGTH(BINARY(6) 'abc');`	`6`
`SELECT OCTET_LENGTH(VARBINARY '');`	`0`
`SELECT OCTET_LENGTH(''::BINARY);`	`1`
`SELECT OCTET_LENGTH(null::VARBINARY);`
`SELECT OCTET_LENGTH(null::BINARY);`

5.35 - OVERLAY

Replaces part of a string with another string and returns the new string value as a VARCHAR.

Behavior type

Immutable if using OCTETS, Stable otherwise

Syntax

OVERLAY ( input-string PLACING replace-string FROM position [ FOR extent ] [ USING { CHARACTERS | OCTETS } ] )

Arguments

input-string

The string to process, of type CHAR or VARCHAR.

replace-string

The string to replace the specified substring of input-string, of type CHAR or VARCHAR.

position

Integer ≥1 that specifies the first character or octet of input-string to overlay replace-string.

extent

Integer that specifies how many characters or octets of input-string to overlay with replace-string. If omitted, OVERLAY uses the length of replace-string.

For example, compare the following calls to OVERLAY:

OVERLAY omits FOR clause. The number of characters replaced in the input string equals the number of characters in replacement string ABC:
```
dbadmin=> SELECT OVERLAY ('123456789' PLACING 'ABC' FROM 5);
  overlay
-----------
 1234ABC89
(1 row)
```
OVERLAY includes a FOR clause that specifies to replace four characters in the input string with the replacement string. The replacement string is three characters long, so OVERLAY returns a string that is one character shorter than the input string:
```
=> SELECT OVERLAY ('123456789' PLACING 'ABC' FROM 5 FOR 4);
 overlay
----------
 1234ABC9
(1 row)
```
OVERLAY includes a FOR clause that specifies to replace -2 characters in the input string with the replacement string. The function returns a string that is two characters longer than the input string:
```
=> SELECT OVERLAY ('123456789' PLACING 'ABC' FROM 5 FOR -2);
    overlay
----------------
 1234ABC3456789
(1 row)
```

USING CHARACTERS | OCTETS

Specifies whether OVERLAY uses characters (default) or octets.

Note

If you specify USING OCTETS, Vertica calls the OVERLAYB function.

Examples

=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2);
  overlay
-----------
 1xxx56789
(1 row)

=> SELECT OVERLAY('123456789' PLACING 'XXX' FROM 2 USING OCTETS);
 overlayb
-----------
 1XXX56789
(1 row)

=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2 FOR 4);
 overlay
----------
 1xxx6789
(1 row)

=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2 FOR 5);
 overlay
---------
 1xxx789
(1 row)

=> SELECT OVERLAY('123456789' PLACING 'xxx' FROM 2 FOR 6);
 overlay
---------
 1xxx89
(1 row)

5.36 - OVERLAYB

Replaces part of a string with another string and returns the new string as an octet value.

The OVERLAYB function treats the multibyte character string as a string of octets (bytes) and use octet numbers as incoming and outgoing position specifiers and lengths. The strings themselves are type VARCHAR, but they treated as if each byte was a separate character.

Behavior type

Immutable

Syntax

OVERLAYB ( input-string, replace-string, position [, extent ] )

Arguments

input-string: The string to process, of type CHAR or VARCHAR.
replace-string: The string to replace the specified substring of input-string, of type CHAR or VARCHAR.
position: Integer ≥1 that specifies the first octet of*input-string* to overlay replace-string.
extent: Integer that specifies how many octets of input-string to overlay with replace-string. If omitted, OVERLAY uses the length of replace-string.

Examples

=> SELECT OVERLAYB('123456789', 'ééé', 2);
 OVERLAYB
----------
 1ééé89
(1 row)
=> SELECT OVERLAYB('123456789', 'ßßß', 2);
 OVERLAYB
----------
 1ßßß89
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2);
 OVERLAYB
-----------
 1xxx56789
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2, 4);
 OVERLAYB
----------
 1xxx6789
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2, 5);
 OVERLAYB
----------
 1xxx789
(1 row)
=> SELECT OVERLAYB('123456789', 'xxx', 2, 6);
 OVERLAYB
----------
 1xxx89
(1 row)

5.37 - POSITION

Returns an INTEGER value representing the character location of a specified substring with a string (counting from one).

Behavior type

Immutable

Syntax 1

POSITION ( substring IN string [ USING { CHARACTERS | OCTETS } ] )

Arguments

substring: (CHAR or VARCHAR) is the substring to locate
string: (CHAR or VARCHAR) is the string in which to locate the substring
USING CHARACTERS | OCTETS: Determines whether the position is reported by using characters (the default) or octets.

Syntax 2

POSITION ( substring IN string )

Arguments

substring: (VARBINARY) is the substring to locate
string: (VARBINARY) is the string in which to locate the substring

Notes

When the string and substring are CHAR or VARCHAR, the return value is based on either the character or octet position of the substring.
When the string and substring are VARBINARY, the return value is always based on the octet position of the substring.
The string and substring must be consistent. Do not mix VARBINARY with CHAR or VARCHAR.
POSITION is similar to STRPOS although POSITION allows finding by characters and by octet.
If the string is not found, the return value is zero.

Examples

=> SELECT POSITION('é' IN 'étudiant' USING CHARACTERS);
 position
----------
        1
(1 row)
=> SELECT POSITION('ß' IN 'straße' USING OCTETS);
 positionb
-----------
         5
(1 row)

=> SELECT POSITION('c' IN 'abcd' USING CHARACTERS);
 position
----------
        3
(1 row)

=> SELECT POSITION(VARBINARY '456' IN VARBINARY '123456789');
 position
----------
        4
(1 row)

SELECT POSITION('n' in 'León') as 'default',
       POSITIONB('León', 'n') as 'POSITIONB',
       POSITION('n' in 'León' USING CHARACTERS) as 'pos_chars',
       POSITION('n' in 'León' USING OCTETS) as 'pos_oct',INSTR('León','n'),
       INSTRB('León','n'), REGEXP_INSTR('León','n');
 default | POSITIONB | pos_chars | pos_oct | INSTR | INSTRB | REGEXP_INSTR
---------+-----------+-----------+---------+-------+--------+--------------
       4 |         5 |         4 |       5 |     4 |      5 |            4
(1 row)

5.38 - POSITIONB

Returns an INTEGER value representing the octet location of a specified substring with a string (counting from one).

Behavior type

Immutable

Syntax

POSITIONB ( string, substring )

Arguments

string: (CHAR or VARCHAR) is the string in which to locate the substring
substring: (CHAR or VARCHAR) is the substring to locate

Examples

=> SELECT POSITIONB('straße', 'ße');
 POSITIONB
-----------
         5
(1 row)


=> SELECT POSITIONB('étudiant', 'é');
 POSITIONB
-----------
         1
(1 row)

5.39 - QUOTE_IDENT

Returns the specified string argument in the format required to use the string as an identifier in an SQL statement.

Returns the specified string argument in the format required to use the string as an identifier in an SQL statement. Quotes are added as needed—for example, if the string contains non-identifier characters or is an SQL or Vertica-reserved keyword:

1time
Next week
SELECT

Embedded double quotes are doubled.

Note

SQL identifiers such as table and column names are stored as created, and references to them are resolved using case-insensitive compares. Thus, you do not need to double-quote mixed-case identifiers.
Vertica quotes all reserved keywords, even if unused.

Behavior type

Immutable

Syntax

QUOTE_IDENT( 'string' )

Arguments

string: String to quote

Examples

Quoted identifiers are case-insensitive, and Vertica does not supply the quotes:

=> SELECT QUOTE_IDENT('VErtIcA');
 QUOTE_IDENT
-------------
 VErtIcA
(1 row)

=> SELECT QUOTE_IDENT('Vertica database');
    QUOTE_IDENT
--------------------
 "Vertica database"
(1 row)

Embedded double quotes are doubled:

=> SELECT QUOTE_IDENT('Vertica "!" database');
       QUOTE_IDENT
--------------------------
 "Vertica ""!"" database"
(1 row)

The following example uses the SQL keyword SELECT, so results are double quoted:

=> SELECT QUOTE_IDENT('select');
 QUOTE_IDENT
-------------
 "select"
(1 row)

5.40 - QUOTE_LITERAL

Returns the given string suitably quoted for use as a string literal in a SQL statement string.

Returns the given string suitably quoted for use as a string literal in a SQL statement string. Embedded single quotes and backslashes are doubled. As per the SQL standard, the function recognizes two consecutive single quotes within a string literal as a single quote character.

Behavior type

Immutable

Syntax

QUOTE_LITERAL ( string )

Arguments

string-expression: Argument that resolves to one or more strings to format as string literals.

Examples

In the following example, the first query returns no first name for Cher or Sting; the second query uses QUOTE_LITERAL, which sets off string values with single quotes, including empty strings. In this case, fname for Sting is set to an empty string (''), while fname for Cher is empty, indicating that it is set to null value:

=> SELECT * FROM lead_vocalists ORDER BY lname ASC;
 fname  |  lname  |                      band
--------+---------+-------------------------------------------------
        | Cher    | ["Sonny and Cher"]
 Mick   | Jagger  | ["Rolling Stones"]
 Diana  | Ross    | ["Supremes"]
 Grace  | Slick   | ["Jefferson Airplane","Jefferson Starship"]
        | Sting   | ["Police"]
 Stevie | Winwood | ["Spencer Davis Group","Traffic","Blind Faith"]
(6 rows)

=> SELECT QUOTE_LITERAL (fname) "First Name", QUOTE_NULLABLE (lname) "Last Name", band FROM lead_vocalists ORDER BY lname ASC;
 First Name | Last Name |                      band
------------+-----------+-------------------------------------------------
            | 'Cher'    | ["Sonny and Cher"]
 'Mick'     | 'Jagger'  | ["Rolling Stones"]
 'Diana'    | 'Ross'    | ["Supremes"]
 'Grace'    | 'Slick'   | ["Jefferson Airplane","Jefferson Starship"]
 ''         | 'Sting'   | ["Police"]
 'Stevie'   | 'Winwood' | ["Spencer Davis Group","Traffic","Blind Faith"]
(6 rows)

5.41 - QUOTE_NULLABLE

Returns the given string suitably quoted for use as a string literal in an SQL statement string; or if the argument is null, returns the unquoted string NULL.

Returns the given string suitably quoted for use as a string literal in an SQL statement string; or if the argument is null, returns the unquoted string NULL. Embedded single-quotes and backslashes are properly doubled.

Behavior type

Immutable

Syntax

QUOTE_NULLABLE ( string-expression )

Arguments

string-expression: Argument that resolves to one or more strings to format as string literals. If string-expression resolves to null value, QUOTE_NULLABLE returns NULL.

Examples

The following examples use the table lead_vocalists, where the first names (fname) for Cher and Sting are set to NULL and an empty string, respectively

=> SELECT * from lead_vocalists ORDER BY lname DESC;
 fname  |  lname  |                      band
--------+---------+-------------------------------------------------
 Stevie | Winwood | ["Spencer Davis Group","Traffic","Blind Faith"]
        | Sting   | ["Police"]
 Grace  | Slick   | ["Jefferson Airplane","Jefferson Starship"]
 Diana  | Ross    | ["Supremes"]
 Mick   | Jagger  | ["Rolling Stones"]
        | Cher    | ["Sonny and Cher"]
(6 rows)

=> SELECT * FROM lead_vocalists WHERE fname IS NULL;
 fname | lname |        band
-------+-------+--------------------
       | Cher  | ["Sonny and Cher"]
(1 row)

=> SELECT * FROM lead_vocalists WHERE fname = '';
 fname | lname |    band
-------+-------+------------
       | Sting | ["Police"]
(1 row)

The following query uses QUOTE_NULLABLE. Like QUOTE_LITERAL, QUOTE_NULLABLE sets off string values with single quotes, including empty strings. Unlike QUOTE_LITERAL, QUOTE_NULLABLE outputs NULL for null values:


=> SELECT QUOTE_NULLABLE (fname) "First Name", QUOTE_NULLABLE (lname) "Last Name", band FROM lead_vocalists ORDER BY fname DESC;
 First Name | Last Name |                      band
------------+-----------+-------------------------------------------------
 NULL       | 'Cher'    | ["Sonny and Cher"]
 'Stevie'   | 'Winwood' | ["Spencer Davis Group","Traffic","Blind Faith"]
 'Mick'     | 'Jagger'  | ["Rolling Stones"]
 'Grace'    | 'Slick'   | ["Jefferson Airplane","Jefferson Starship"]
 'Diana'    | 'Ross'    | ["Supremes"]
 ''         | 'Sting'   | ["Police"]
(6 rows)

5.42 - REPEAT

Replicates a string the specified number of times and concatenates the replicated values as a single string.

Replicates a string the specified number of times and concatenates the replicated values as a single string. The return value takes on the data type of the string argument. Return values for non-LONG data types and LONG data types can be up to 65000 and 32000000 bytes in length, respectively. If the length of string*count exceeds these limits, Vertica silently truncates the results.

Behavior type

Immutable

Syntax

REPEAT ( 'string', count )

Arguments

string

The string to repeat, one of the following:

CHAR
VARCHAR
BINARY
VARBINARY
LONG VARCHAR
LONG VARBINARY

count

An integer expression that specifies how many times to repeat string.

Examples

The following example repeats vmart three times:

=> SELECT REPEAT ('vmart', 3);
     REPEAT
-----------------
 vmartvmartvmart
(1 row)

5.43 - REPLACE

Replaces all occurrences of characters in a string with another set of characters.

Behavior type

Immutable

Syntax

REPLACE ('string', 'target', 'replacement' )

Arguments

string: The string to modify.
target: The characters in string to replace.
replacement: The characters to replace target.

Examples

=> SELECT REPLACE('Documentation%20Library', '%20', ' ');
        REPLACE
-----------------------
 Documentation Library
(1 row)

=> SELECT REPLACE('This &amp; That', '&amp;', 'and');
    REPLACE
---------------
 This and That
(1 row)

=> SELECT REPLACE('straße', 'ß', 'ss');
 REPLACE
---------
 strasse
(1 row)

5.44 - RIGHT

Returns the specified characters from the right side of a string.

Behavior type

Immutable

Syntax

RIGHT ( string-expr, length )

Arguments

string-expr: The string expression to return.
length: An integer value that specifies how many characters to return.

Examples

The following query returns the last three characters of the string 'vertica':

=> SELECT RIGHT('vertica', 3);
 RIGHT
-------
 ica
(1 row)

The following query queries date column date_ordered from table store.store_orders_fact. It coerces the dates to strings and extracts the last five characters from each string. It then returns all distinct strings:

SELECT DISTINCT(
  RIGHT(date_ordered::varchar, 5)) MonthDays
  FROM store.store_orders_fact ORDER BY MonthDays;
 MonthDays
-----------
 01-01
 01-02
 01-03
 01-04
 01-05
 01-06
 01-07
 01-08
 01-09
 01-10
 02-01
 02-02
 02-03
 ...
 11-08
 11-09
 11-10
 12-01
 12-02
 12-03
 12-04
 12-05
 12-06
 12-07
 12-08
 12-09
 12-10
(120 rows)

5.45 - RPAD

Returns a VARCHAR value representing a string of a specific length filled on the right with specific characters.

Behavior type

Immutable

Syntax

RPAD ( expression , length [ , fill ] )

Arguments

expression: (CHAR OR VARCHAR) specifies the string to fill
length: (INTEGER) specifies the number of characters to return
fill: (CHAR OR VARCHAR) specifies the repeating string of characters with which to fill the output string. The default is the space character.

Examples

=> SELECT RPAD('database', 15, 'xzy');
      RPAD
-----------------
 databasexzyxzyx
(1 row)

If the string is already longer than the specified length it is truncated on the right:

=> SELECT RPAD('database', 6, 'xzy');
  RPAD
--------
 databa
(1 row)

5.46 - RTRIM

Returns a VARCHAR value representing a string with trailing blanks removed from the right side (end).

Behavior type

Immutable

Syntax

RTRIM ( expression [ , characters ] )

Arguments

expression: (CHAR or VARCHAR) is the string to trim
characters: (CHAR or VARCHAR) specifies the characters to remove from the right side of expression. The default is the space character.

Examples

=> SELECT RTRIM('trimzzzyyyyyyxxxxxxxx', 'xyz');
 RTRIM
-------
 trim
(1 row)

5.47 - SHA1

Uses the US Secure Hash Algorithm 1 to calculate the SHA1 hash of string.

Uses the US Secure Hash Algorithm 1 to calculate the SHA1 hash of string. Returns the result as a VARCHAR string in hexadecimal.

Behavior type

Immutable

Syntax

SHA1 ( string )

Arguments

string: The VARCHAR or VARBINARY string to be calculated.

Examples

The following examples calculate the SHA1 hash of the provided strings:

=> SELECT SHA1('123');
                   SHA1
------------------------------------------
 40bd001563085fc35165329ea1ff5c5ecbdbbeef
(1 row)

=> SELECT SHA1('Vertica'::bytea);
                   SHA1
------------------------------------------
 ee2cff8d3444995c6c301546c4fc5ee152d77c11
(1 row)

5.48 - SHA224

Uses the US Secure Hash Algorithm 2 to calculate the SHA224 hash of string.

Uses the US Secure Hash Algorithm 2 to calculate the SHA224 hash of string. Returns the result as a VARCHAR string in hexadecimal.

Behavior type

Immutable

Syntax

SHA224 ( string )

Arguments

string: The VARCHAR or VARBINARY string to be calculated.

Examples

The following examples calculate the SHA224 hash of the provided strings:

=> SELECT SHA224('abc');
                      SHA224
----------------------------------------------------------
78d8045d684abd2eece923758f3cd781489df3a48e1278982466017f
(1 row)

=> SELECT SHA224('Vertica'::bytea);
                      SHA224
----------------------------------------------------------
 135ac268f64ff3124aeeebc3cc0af0a29fd600a3be8e29ed97e45e25
(1 row)

=> SELECT sha224(''::varbinary) = 'd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f' AS "TRUE";
 TRUE
------
t
(1 row)

5.49 - SHA256

Uses the US Secure Hash Algorithm 2 to calculate the SHA256 hash of string.

Uses the US Secure Hash Algorithm 2 to calculate the SHA256 hash of string. Returns the result as a VARCHAR string in hexadecimal.

Behavior type

Immutable

Syntax

SHA256 ( string )

Arguments

string: The VARCHAR or VARBINARY string to be calculated.

Examples

The following examples calculate the SHA256 hash of the provided strings:

=> SELECT SHA256('abc');
                              SHA256
------------------------------------------------------------------
 a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3
(1 row)

=> SELECT SHA256('Vertica'::bytea);
                              SHA256
------------------------------------------------------------------
 9981b0b7df9f5be06e9e1a7f4ae2336a7868d9ab522b9a6ca6a87cd9ed95ba53
(1 row)

=> SELECT sha256('') = 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855' AS "TRUE";
 TRUE
------
t
(1 row)

5.50 - SHA384

Uses the US Secure Hash Algorithm 2 to calculate the SHA384 hash of string.

Uses the US Secure Hash Algorithm 2 to calculate the SHA384 hash of string. Returns the result as a VARCHAR string in hexadecimal.

Behavior type

Immutable

Syntax

SHA384 ( string )

Arguments

string: The VARCHAR or VARBINARY string to be calculated.

Examples

The following examples calculate the SHA384 hash of the provided strings:

=> SELECT SHA384('123');
                                              SHA384
--------------------------------------------------------------------------------------------------
 9a0a82f0c0cf31470d7affede3406cc9aa8410671520b727044eda15b4c25532a9b5cd8aaf9cec4919d76255b6bfb00f
(1 row)

=> SELECT SHA384('Vertica'::bytea);
                                              SHA384
--------------------------------------------------------------------------------------------------
 3431a717dc3289862bbd636a064d26980b47ebe4684b800cff4756f0c24985866ef97763eafd548fedb0ce28722c96bb
(1 row)

5.51 - SHA512

Uses the US Secure Hash Algorithm 2 to calculate the SHA512 hash of string.

Uses the US Secure Hash Algorithm 2 to calculate the SHA512 hash of string. Returns the result as a VARCHAR string in hexadecimal.

Behavior type

Immutable

Syntax

SHA512 ( string )

Arguments

string: The VARCHAR or VARBINARY string to be calculated.

Examples

The following examples calculate the SHA512 hash of the provided strings:

=> SELECT SHA512('123');
                                                          SHA512
----------------------------------------------------------------------------------------------------------------------------------
 3c9909afec25354d551dae21590bb26e38d53f2173b8d3dc3eee4c047e7ab1c1eb8b85103e3be7ba613b31bb5c9c36214dc9f14a42fd7a2fdb84856bca5c44c2

(1 row)

=> SELECT SHA512('Vertica'::bytea);
                                                          SHA512
----------------------------------------------------------------------------------------------------------------------------------
 c4ee2b2d17759226a3897c9c30d7c6df1145c4582849bb5191ee140bce05b83d3d869890cc3619b534fea6f97ff28a739d8b568a5ade66e756b3243ef97d3f00
(1 row)

5.52 - SOUNDEX

Takes a VARCHAR argument and returns a four-character code that enables comparison of that argument with other SOUNDEX-encoded strings that are spelled differently in English, but are phonetically similar. SOUNDEX implements an algorithm that was developed by Robert C. Russell and Margaret King Odell, and is described in The Art of Computer Programming, Vol. 3.

Behavior type

Immutable

Syntax

SOUNDEX ( string-expression )

Arguments

string-expression: The VARCHAR expression to encode.

Soundex encoding algorithm

Vertica uses the following Soundex encoding algorithm, which complies with most SQL implementations:

Save the first letter. Map all occurrences of a, e, i, o, u, y, h, w to zero (0).
Replace all consonants (include the first letter) with digits:
- b, f, p, v → 1
- c, g, j, k, q, s, x, z → 2
- d, t → 3
- l → 4
- m, n → 5
- r → 6
Replace all adjacent same digits with one digit, and then remove all zero (0) digits
If the saved letter's digit is the same as the resulting first digit, remove the digit (keep the letter).
Append 3 zeros if result contains less than 3 digits. Remove all except first letter and 3 digits after it.

Note

Encoding ignores all non-alphabetic characters—for example, the apostrophe in O'Connor.

Examples

Find last names in the employee_dimension table that are phonetically similar to Lee:

SELECT employee_last_name, employee_first_name, employee_state
    FROM public.employee_dimension
    WHERE SOUNDEX(employee_last_name) = SOUNDEX('Lee')
    ORDER BY employee_state, employee_last_name, employee_first_name;
 Lea                | James               | AZ
 Li                 | Sam                 | AZ
 Lee                | Darlene             | CA
 Lee                | Juanita             | CA
 Li                 | Amy                 | CA
 Li                 | Barbara             | CA
 Li                 | Ben                 | CA
 ...

5.53 - SOUNDEX_MATCHES

Compares the Soundex encodings of two strings.

Compares the Soundex encodings of two strings. The function then returns an integer that indicates the number of matching characters, in the same order. The return value is 0 to 4 inclusive, where 0 indicates no match, and 4 an exact match.

For details on how Vertica implements Soundex encoding, see Soundex Encoding Algorithm.

Behavior type

Immutable

Syntax

SOUNDEX_MATCHES ( string-expression1, string-expression2 )

Arguments

string-expression1, string-expression2: The two VARCHAR expressions to encode and compare.

Examples

Find how well the Soundex encodings of two strings match:

Compare the Soundex encodings of Lewis and Li:

> SELECT SOUNDEX_MATCHES('Lewis', 'Li');
 SOUNDEX_MATCHES
-----------------
               3
(1 row)

Compare the Soundex encodings of Lee and Li:

=> SELECT SOUNDEX_MATCHES('Lee', 'Li');
 SOUNDEX_MATCHES
-----------------
               4
(1 row)

Find last names in the employee_dimension table whose Soundex encodings match at least 3 characters in the encoding for Lewis:

=> SELECT DISTINCT(employee_last_name)
      FROM public.employee_dimension
      WHERE SOUNDEX_MATCHES (employee_last_name, 'Lewis' ) >= 3 ORDER BY employee_last_name;
 employee_last_name
--------------------
 Lea
 Lee
 Leigh
 Lewis
 Li
 Reyes
(6 rows)

5.54 - SPACE

Returns the specified number of blank spaces, typically for insertion into a character string.

Behavior type

Immutable

Syntax

SPACE(n)

Arguments

n: An integer argument that specifies how many spaces to insert.

Examples

The following example concatenates strings x and y with 10 spaces inserted between them:

=> SELECT 'x' || SPACE(10) || 'y' AS Ten_spaces;
  Ten_spaces
--------------
 x          y
(1 row)

5.55 - SPLIT_PART

Splits string on the delimiter and returns the string at the location of the beginning of the specified field (counting from 1).

Behavior type

Immutable

Syntax

SPLIT_PART ( string , delimiter , field )

Arguments

string: Argument string
delimiter: Delimiter
field: (INTEGER) Number of the part to return

Notes

Use this with the character form of the subfield.

Examples

The specified integer of 2 returns the second string, or def.

=> SELECT SPLIT_PART('abc~@~def~@~ghi', '~@~', 2);
 SPLIT_PART
------------
 def
(1 row)

In the next example, specify 3, which returns the third string, or 789.

=> SELECT SPLIT_PART('123~|~456~|~789', '~|~', 3);
 SPLIT_PART
------------
 789
(1 row)

The tildes are for readability only. Omitting them returns the same results:

=> SELECT SPLIT_PART('123|456|789', '|', 3);
 SPLIT_PART
------------
 789
(1 row)

See what happens if you specify an integer that exceeds the number of strings: The result is not null, it is an empty string.

=> SELECT SPLIT_PART('123|456|789', '|', 4);
 SPLIT_PART
------------

(1 row)

=> SELECT SPLIT_PART('123|456|789', '|', 4) IS NULL;
 ?column?
----------
 f
(1 row)

If SPLIT_PART had returned NULL, LENGTH would have returned 0.

=> SELECT LENGTH (SPLIT_PART('123|456|789', '|', 4));
 LENGTH
--------
      0
(1 row)

If the locale of your database is BINARY, SPLIT_PART calls SPLIT_PARTB:

=> SHOW LOCALE;
  name  |               setting
--------+--------------------------------------
 locale | en_US@collation=binary (LEN_KBINARY)
(1 row)
=> SELECT SPLIT_PART('123456789', '5', 1);
 split_partb
-------------
 1234
(1 row)

=> SET LOCALE TO 'en_US@collation=standard';
INFO 2567:  Canonical locale: 'en_US@collation=standard'
Standard collation: 'LEN'
English (United States, collation=standard)
SET

=> SELECT SPLIT_PART('123456789', '5', 1);
 split_part
------------
 1234
(1 row)

5.56 - SPLIT_PARTB

Divides an input string on a delimiter character and returns the Nth segment, counting from 1.

Divides an input string on a delimiter character and returns the Nth segment, counting from 1. The VARCHAR arguments are treated as octets rather than UTF-8 characters.

Behavior type

Immutable

Syntax

SPLIT_PARTB ( string, delimiter, part-number)

Arguments

string: VARCHAR, the string to split.
delimiter: VARCHAR, the delimiter between segments.
part-number: INTEGER, the part number to return. The first part is 1, not 0.

Examples

The following example returns the third part of its input:

=> SELECT SPLIT_PARTB('straße~@~café~@~soupçon', '~@~', 3);
 SPLIT_PARTB
-------------
 soupçon
(1 row)

The tildes are for readability only. Omitting them returns the same results:

=> SELECT SPLIT_PARTB('straße @ café @ soupçon', '@', 3);
 SPLIT_PARTB
-------------
  soupçon
(1 row)

If the requested part number is greater than the number of parts, the function returns an empty string:

=> SELECT SPLIT_PARTB('straße @ café @ soupçon', '@', 4);
 SPLIT_PARTB
-------------

(1 row)

=> SELECT SPLIT_PARTB('straße @ café @ soupçon', '@', 4) IS NULL;
 ?column?
----------
 f
(1 row)

If the locale of your database is BINARY, SPLIT_PART calls SPLIT_PARTB:

=> SHOW LOCALE;
  name  |               setting
--------+--------------------------------------
 locale | en_US@collation=binary (LEN_KBINARY)
(1 row)
=> SELECT SPLIT_PART('123456789', '5', 1);
 split_partb
-------------
 1234
(1 row)

=> SET LOCALE TO 'en_US@collation=standard';
INFO 2567:  Canonical locale: 'en_US@collation=standard'
Standard collation: 'LEN'
English (United States, collation=standard)
SET

=> SELECT SPLIT_PART('123456789', '5', 1);
 split_part
------------
 1234
(1 row)

5.57 - STRPOS

Returns an INTEGER value that represents the location of a specified substring within a string (counting from one).

Returns an INTEGER value that represents the location of a specified substring within a string (counting from one). If the substring is not found, STRPOS returns 0.

STRPOS is similar to POSITION; however, POSITION allows finding by characters and by octet.

Behavior type

Immutable

Syntax

STRPOS ( string-expression , substring )

Arguments

string-expression: The string in which to locate substring
substring: The substring to locate in string-expression

Examples

=> SELECT ship_type, shipping_key, strpos (ship_type, 'DAY') FROM shipping_dimension WHERE strpos > 0 ORDER BY ship_type, shipping_key;
           ship_type            | shipping_key | strpos
--------------------------------+--------------+--------
 NEXT DAY                       |            1 |      6
 NEXT DAY                       |           13 |      6
 NEXT DAY                       |           19 |      6
 NEXT DAY                       |           22 |      6
 NEXT DAY                       |           26 |      6
 NEXT DAY                       |           30 |      6
 NEXT DAY                       |           34 |      6
 NEXT DAY                       |           38 |      6
 NEXT DAY                       |           45 |      6
 NEXT DAY                       |           51 |      6
 NEXT DAY                       |           67 |      6
 NEXT DAY                       |           69 |      6
 NEXT DAY                       |           80 |      6
 NEXT DAY                       |           90 |      6
 NEXT DAY                       |           96 |      6
 NEXT DAY                       |           98 |      6
 TWO DAY                        |            9 |      5
 TWO DAY                        |           21 |      5
 TWO DAY                        |           28 |      5
 TWO DAY                        |           32 |      5
 TWO DAY                        |           40 |      5
 TWO DAY                        |           43 |      5
 TWO DAY                        |           49 |      5
 TWO DAY                        |           50 |      5
 TWO DAY                        |           52 |      5
 TWO DAY                        |           53 |      5
 TWO DAY                        |           61 |      5
 TWO DAY                        |           73 |      5
 TWO DAY                        |           81 |      5
 TWO DAY                        |           83 |      5
 TWO DAY                        |           84 |      5
 TWO DAY                        |           85 |      5
 TWO DAY                        |           94 |      5
 TWO DAY                        |          100 |      5
(34 rows)

5.58 - STRPOSB

Returns an INTEGER value representing the location of a specified substring within a string, counting from one, where each octet in the string is counted (as opposed to characters).

Behavior type

Immutable

Syntax

STRPOSB ( string , substring )

Arguments

string: (CHAR or VARCHAR) is the string in which to locate the substring
substring: (CHAR or VARCHAR) is the substring to locate

Notes

STRPOSB is identical to POSITIONB except for the order of the arguments.

Examples

=> SELECT STRPOSB('straße', 'e');
 STRPOSB
---------
       7
(1 row)


=> SELECT STRPOSB('étudiant', 'tud');
 STRPOSB
---------
       3
(1 row)

5.59 - SUBSTR

Returns VARCHAR or VARBINARY value representing a substring of a specified string.

Behavior type

Immutable

Syntax

SUBSTR ( string , position [ , extent ] )

Arguments

string: (CHAR/VARCHAR or BINARY/VARBINARY) is the string from which to extract a substring. If null, Vertica returns no results.
position: (INTEGER or DOUBLE PRECISION) is the starting position of the substring (counting from one by characters). If 0 or negative, Vertica returns no results.
extent: (INTEGER or DOUBLE PRECISION) is the length of the substring to extract (in characters). The default is the end of the string.

Notes

SUBSTR truncates DOUBLE PRECISION input values.

Examples

=> SELECT SUBSTR('abc'::binary(3),1);
 substr
--------
 abc
(1 row)

=> SELECT SUBSTR('123456789', 3, 2);
 substr
--------
 34
(1 row)

=> SELECT SUBSTR('123456789', 3);
 substr
---------
 3456789
(1 row)

=> SELECT SUBSTR(TO_BITSTRING(HEX_TO_BINARY('0x10')), 2, 2);
 substr
--------
 00
(1 row)

=> SELECT SUBSTR(TO_HEX(10010), 2, 2);
 substr
--------
 71
(1 row)

5.60 - SUBSTRB

Returns an octet value representing the substring of a specified string.

Behavior type

Immutable

Syntax

SUBSTRB ( string , position [ , extent ] )

Arguments

string: (CHAR/VARCHAR) is the string from which to extract a substring.
position: (INTEGER or DOUBLE PRECISION) is the starting position of the substring (counting from one in octets).
extent: (INTEGER or DOUBLE PRECISION) is the length of the substring to extract (in octets). The default is the end of the string

Notes

This function treats the multibyte character string as a string of octets (bytes) and uses octet numbers as incoming and outgoing position specifiers and lengths. The strings themselves are type VARCHAR, but they treated as if each octet were a separate character.
SUBSTRB truncates DOUBLE PRECISION input values.

Examples

=> SELECT SUBSTRB('soupçon', 5);
 SUBSTRB
---------
 çon
(1 row)

=> SELECT SUBSTRB('soupçon', 5, 2);
 SUBSTRB
---------
 ç
(1 row)

Vertica returns the following error message if you use BINARY/VARBINARY:

=>SELECT SUBSTRB('abc'::binary(3),1);
ERROR: function substrb(binary, int) does not exist, or permission is denied for substrb(binary, int)
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

5.61 - SUBSTRING

Returns a value representing a substring of the specified string at the given position, given a value, a position, and an optional length.

Returns a value representing a substring of the specified string at the given position, given a value, a position, and an optional length. SUBSTRING truncates DOUBLE PRECISION input values.

Behavior type

Immutable if USING OCTETS, stable otherwise.

Syntax

SUBSTRING ( string, position[, length ]
    [USING {CHARACTERS | OCTETS } ] )
SUBSTRING ( string FROM position [ FOR length ]
    [USING { CHARACTERS | OCTETS } ] )

Arguments

string: (CHAR/VARCHAR or BINARY/VARBINARY) is the string from which to extract a substring
position: (INTEGER or DOUBLE PRECISION) is the starting position of the substring (counting from one by either characters or octets). (The default is characters.) If position is greater than the length of the given value, an empty value is returned.
length: (INTEGER or DOUBLE PRECISION) is the length of the substring to extract in either characters or octets. (The default is characters.) The default is the end of the string.If a length is given the result is at most that many bytes. The maximum length is the length of the given value less the given position. If no length is given or if the given length is greater than the maximum length then the length is set to the maximum length.
USING CHARACTERS | OCTETS: Determines whether the value is expressed in characters (the default) or octets.

Examples

=> SELECT SUBSTRING('abc'::binary(3),1);
 substring
-----------
 abc
(1 row)

=> SELECT SUBSTRING('soupçon', 5, 2 USING CHARACTERS);
 substring
-----------
 ço
(1 row)

=> SELECT SUBSTRING('soupçon', 5, 2 USING OCTETS);
 substring
-----------
 ç
(1 row)

If you use a negative position, then the functions starts at a non-existent position. In this example, that means counting eight characters starting at position -4. So the function starts at the empty position -4 and counts five characters, including a position for zero which is also empty. This returns three characters.

=> SELECT SUBSTRING('1234567890', -4, 8);
 substring
 -----------
 123
(1 row)

5.62 - TRANSLATE

Replaces individual characters in string_to_replace with other characters.

Replaces individual characters in string_to_replace with other characters.

Behavior type

Immutable

Syntax

TRANSLATE ( string_to_replace , from_string , to_string );

Arguments

string_to_replace: String to be translated.
from_string: Contains characters that should be replaced in string_to_replace.
to_string: Any character in string_to_replace that matches a character in from_string is replaced by the corresponding character in to_string.

Examples

=> SELECT TRANSLATE('straße', 'ß', 'ss');
 TRANSLATE
-----------
 strase
(1 row)

5.63 - TRIM

Combines the BTRIM, LTRIM, and RTRIM functions into a single function.

Behavior type

Immutable

Syntax

TRIM ( [ [ LEADING | TRAILING | BOTH ] [ characters ] FROM ] expression )

Arguments

LEADING: Removes the specified characters from the left side of the string
TRAILING: Removes the specified characters from the right side of the string
BOTH: Removes the specified characters from both sides of the string (default)
characters: (CHAR or VARCHAR) specifies the characters to remove from expression. The default is the space character.
expression: (CHAR or VARCHAR) is the string to trim

Examples

=> SELECT '-' || TRIM(LEADING 'x' FROM 'xxdatabasexx') || '-';
   ?column?
--------------
 -databasexx-
(1 row)

=> SELECT '-' || TRIM(TRAILING 'x' FROM 'xxdatabasexx') || '-';
   ?column?
--------------
 -xxdatabase-
(1 row)

=> SELECT '-' || TRIM(BOTH 'x' FROM 'xxdatabasexx') || '-';
  ?column?
------------
 -database-
(1 row)

=> SELECT '-' || TRIM('x' FROM 'xxdatabasexx') || '-';
  ?column?
------------
 -database-
(1 row)

=> SELECT '-' || TRIM(LEADING FROM '  database  ') || '-';
   ?column?
--------------
 -database  -
(1 row)

=> SELECT '-' || TRIM('  database  ') || '-';  ?column?
------------
 -database-
(1 row)

5.64 - UPPER

Returns a VARCHAR value containing the argument converted to uppercase letters.

Starting in Release 5.1, this function treats the string argument as a UTF-8 encoded string, rather than depending on the collation setting of the locale (for example, collation=binary) to identify the encoding.

Behavior type

stable

Syntax

UPPER ( expression )

Arguments

expression: CHAR or VARCHAR containing the string to convert

Notes

UPPER is restricted to 32500 octet inputs, since it is possible for the UTF-8 representation of result to double in size.

Examples

=> SELECT UPPER('AbCdEfG');
  UPPER
----------
 ABCDEFG
(1 row)
=> SELECT UPPER('étudiant');
  UPPER
----------
 ÉTUDIANT
(1 row)

5.65 - UPPERB

Returns a character string with each ASCII character converted to uppercase.

Returns a character string with each ASCII character converted to uppercase. Multibyte characters are not converted and are skipped.

Behavior type

Immutable

Syntax

UPPERB ( expression )

Arguments

expression: (CHAR or VARCHAR) is the string to convert

Examples

In the following example, the multibyte UTF-8 character é is not converted to uppercase:

=> SELECT UPPERB('étudiant');
  UPPERB
----------
 éTUDIANT
(1 row)

=> SELECT UPPERB('AbCdEfG');
 UPPERB
---------
 ABCDEFG
(1 row)

=> SELECT UPPERB('The Vertica Database');
        UPPERB
----------------------
 THE VERTICA DATABASE
(1 row)

6 - URI functions

The functions in this section follow the RFC 3986 standard for percent-encoding a Universal Resource Identifier (URI).

6.1 - URI_PERCENT_DECODE

Decodes a percent-encoded Universal Resource Identifier (URI) according to the RFC 3986 standard.

Syntax

URI_PERCENT_DECODE (expression)

Behavior type

Immutable

Parameters

expression: (VARCHAR) is the string to convert.

Examples

The following example invokes uri_percent_decode on the Websites column of the URI table and returns a decoded URI:

=> SELECT URI_PERCENT_DECODE(Websites) from URI;
              URI_PERCENT_DECODE
-----------------------------------------------
 http://www.faqs.org/rfcs/rfc3986.html x xj%a%
(1 row)

The following example returns the original URI in the Websites column and its decoded version:

=> SELECT Websites, URI_PERCENT_DECODE (Websites) from URI;

                     Websites                      |              URI_PERCENT_DECODE
---------------------------------------------------+---------------------------------------------
 http://www.faqs.org/rfcs/rfc3986.html+x%20x%6a%a% | http://www.faqs.org/rfcs/rfc3986.html x xj%a%

(1 row)

6.2 - URI_PERCENT_ENCODE

Encodes a Universal Resource Identifier (URI) according to the RFC 3986 standard for percent encoding.

Encodes a Universal Resource Identifier (URI) according to the RFC 3986 standard for percent encoding. For compatibility with older encoders, this function converts + to space; space is converted to %20.

Syntax

URI_PERCENT_ENCODE (expression)

Behavior type

Immutable

Parameters

expression: (VARCHAR) is the string to convert.

Examples

The following example shows how the uri_percent_encode function is invoked on a the Websites column of the URI table and returns an encoded URI:

=> SELECT URI_PERCENT_ENCODE(Websites) from URI;
            URI_PERCENT_ENCODE
------------------------------------------
 http%3A%2F%2Fexample.com%2F%3F%3D11%2F15
(1 row)

The following example returns the original URI in the Websites column and it's encoded form:

=> SELECT Websites, URI_PERCENT_ENCODE(Websites) from URI;          Websites          |            URI_PERCENT_ENCODE
----------------------------+------------------------------------------
 http://example.com/?=11/15 | http%3A%2F%2Fexample.com%2F%3F%3D11%2F15
(1 row)

7 - UUID functions

Currently, Vertica provides one function to support UUID data types, UUID_GENERATE.

Currently, Vertica provides one function to support UUID data types, UUID_GENERATE.

7.1 - UUID_GENERATE

Returns a new universally unique identifier (UUID) that is generated based on high-quality randomness from /dev/urandom.

Returns a new universally unique identifier (UUID) that is generated based on high-quality randomness from /dev/urandom.

Behavior type

Volatile

Syntax

UUID_GENERATE()

Examples

=> CREATE TABLE Customers(
     cust_id UUID DEFAULT UUID_GENERATE(),
   lname VARCHAR(36),
   fname VARCHAR(24));
CREATE TABLE
=> INSERT INTO Customers VALUES (DEFAULT, 'Kearney', 'Thomas');
 OUTPUT
--------
      1
(1 row)

=> COPY Customers (lname, fname) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Pham|Duc
>> Garcia|Mary
>> \.
=> SELECT * FROM Customers;
               cust_id                |  lname  | fname
--------------------------------------+---------+--------
 03fe0794-ac5d-42d4-8246-54f7ec81ed0c | Pham    | Duc
 6950313d-c77e-4c11-a86e-0a54aa3ec114 | Kearney | Thomas
 9c9653ce-c2e4-4441-b0f7-0137b54cc28c | Garcia  | Mary
(3 rows)