This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

String functions

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

1: ASCII
2: BIT_LENGTH
3: BITCOUNT
4: BITSTRING_TO_BINARY
5: BTRIM
6: CHARACTER_LENGTH
7: CHR
8: COLLATION
9: CONCAT
10: DECODE
11: EDIT_DISTANCE
12: GREATEST
13: GREATESTB
14: HEX_TO_BINARY
15: HEX_TO_INTEGER
16: INITCAP
17: INITCAPB
18: INSERT
19: INSTR
20: INSTRB
21: ISUTF8
22: LEAST
23: LEASTB
24: LEFT
25: LENGTH
26: LOWER
27: LOWERB
28: LPAD
29: LTRIM
30: MAKEUTF8
31: MD5
32: OCTET_LENGTH
33: OVERLAY
34: OVERLAYB
35: POSITION
36: POSITIONB
37: QUOTE_IDENT
38: QUOTE_LITERAL
39: QUOTE_NULLABLE
40: REPEAT
41: REPLACE
42: RIGHT
43: RPAD
44: RTRIM
45: SHA1
46: SHA224
47: SHA256
48: SHA384
49: SHA512
50: SOUNDEX
51: SOUNDEX_MATCHES
52: SPACE
53: SPLIT_PART
54: SPLIT_PARTB
55: STRPOS
56: STRPOSB
57: SUBSTR
58: SUBSTRB
59: SUBSTRING
60: TRANSLATE
61: TRIM
62: UPPER
63: UPPERB

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.

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)

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`

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)

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 - 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)

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)

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)

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ß

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)

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)

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.

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)

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

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

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"

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"

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);`

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);`

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)

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)

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)

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)

22 - 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
...

23 - 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
...

24 - 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)

25 - 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`

26 - 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)

27 - 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)

28 - 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)

29 - 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)

30 - 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;

31 - 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)

32 - 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);`

33 - 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)

34 - 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)

35 - 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)

36 - 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)

37 - 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)

38 - 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)

39 - 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)

40 - 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)

41 - 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)

42 - 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)

43 - 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)

44 - 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)

45 - 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)

46 - 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)

47 - 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)

48 - 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)

49 - 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)

50 - 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
 ...

51 - 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)

52 - 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)

53 - 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)

54 - SPLIT_PARTB

Splits string on the delimiter and rreturns the string at the location of the beginning of the specified field (counting from 1).

Splits string on the delimiter and rreturns the string at the location of the beginning of the specified field (counting from 1). The VARCHAR arguments are treated as octets rather than UTF-8 characters.

Behavior type

Immutable

Syntax

SPLIT_PARTB ( string , delimiter , field )

Arguments

string: (VARCHAR) Is the argument string.
delimiter: (VARCHAR) Is the given delimiter.
field: (INTEGER) is the number of the part to return.

Notes

Use this function with the character form of the subfield.

Examples

The specified integer of 3 returns the third string, or soupçon.

=> 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)

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_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)

55 - 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)

56 - 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)

57 - 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)

58 - 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.

59 - 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)

60 - 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)

61 - 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)

62 - 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)

63 - 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)

String functions

1 - ASCII

Behavior type

Syntax

Arguments

Examples

2 - BIT_LENGTH

Behavior type

Syntax

Arguments

Examples

See also

3 - BITCOUNT

Behavior type

Syntax

Arguments

Examples

4 - BITSTRING_TO_BINARY

Behavior type

Syntax

Arguments

Examples

5 - BTRIM

Behavior type

Syntax

Arguments

Examples

See also

6 - CHARACTER_LENGTH

Behavior type

Syntax

Arguments

Examples

See also

7 - CHR

Behavior type

Syntax

Arguments

Notes

Examples

8 - COLLATION

Syntax

Arguments

Locales

Binary and non-binary collations

Examples

Collating GROUP BY results

Comparing strings with an equality clause

Sorting strings in non-english languages

9 - CONCAT

Syntax

Behavior type

Arguments

Examples

10 - DECODE

Behavior type

Syntax

Arguments

Examples

11 - EDIT_DISTANCE

Behavior type

Syntax

Arguments

Examples

12 - GREATEST

Behavior type

Syntax

Arguments

Examples

See also

13 - GREATESTB

Behavior type

Syntax

Arguments

Examples

See also

14 - HEX_TO_BINARY

Behavior type

Syntax

Arguments