String functions

• 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: JARO_DISTANCE
• 23: JARO_WINKLER_DISTANCE
• 24: LEAST
• 25: LEASTB
• 26: LEFT
• 27: LENGTH
• 28: LOWER
• 29: LOWERB
• 30: LPAD
• 31: LTRIM
• 32: MAKEUTF8
• 33: MD5
• 34: OCTET_LENGTH
• 35: OVERLAY
• 36: OVERLAYB
• 37: POSITION
• 38: POSITIONB
• 39: QUOTE_IDENT
• 40: QUOTE_LITERAL
• 41: QUOTE_NULLABLE
• 42: REPEAT
• 43: REPLACE
• 44: RIGHT
• 45: RPAD
• 46: RTRIM
• 47: SHA1
• 48: SHA224
• 49: SHA256
• 50: SHA384
• 51: SHA512
• 52: SOUNDEX
• 53: SOUNDEX_MATCHES
• 54: SPACE
• 55: SPLIT_PART
• 56: SPLIT_PARTB
• 57: STRPOS
• 58: STRPOSB
• 59: SUBSTR
• 60: SUBSTRB
• 61: SUBSTRING
• 62: TRANSLATE
• 63: TRIM
• 64: UPPER
• 65: UPPERB

1 - ASCII

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

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. BIT_LENGTH applies to the contents of VARCHAR and VARBINARY fields.

Behavior type

Syntax

BIT_LENGTH ( expression )

Arguments

expression

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

Examples

See also

• CHARACTER_LENGTH
• LENGTH
• OCTET_LENGTH

3 - BITCOUNT

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

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. This function is the inverse of TO_BITSTRING.

Behavior type

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

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)

See also

• LTRIM
• RTRIM
• TRIM

6 - CHARACTER_LENGTH

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)

See also

• BIT_LENGTH

7 - CHR

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

Behavior type

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. 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. If either argument is null, concat returns null.

Syntax

CONCAT ('string-expression1, string-expression2)

Behavior type

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

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

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:

1. kitten → knitten (insert n after k)

2. knitten → knittin (substitute i for e)

3. knittin → knitting (append g)

12 - GREATEST

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

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

See also

13 - GREATESTB

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

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

See also

14 - HEX_TO_BINARY

Translates the given VARCHAR hexadecimal representation into a VARBINARY value.

Behavior type

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"

See also

• TO_HEX

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

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"

See also

• TO_HEX
• HEX_TO_BINARY

16 - INITCAP

Capitalizes first letter of each alphanumeric word and puts the rest in lowercase.

Behavior type

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

17 - INITCAPB

Capitalizes first letter of each alphanumeric word and puts the rest in lowercase. Multibyte characters are not converted and are skipped.

Behavior type

Syntax

INITCAPB ( expression )

Arguments

expression

(VARCHAR) is the string to format.

Notes

Depends on collation setting of the locale.

Examples

18 - INSERT

Inserts a character string into a specified location in another character string.

Syntax

INSERT( 'string1', n, m, 'string2' )

Behavior type

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. The return value is based on the character position of the identified character.

Behavior type

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.

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. The return value is based on the octet position of the identified byte.

Behavior type

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)

See also

• INSTR

21 - ISUTF8

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

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

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)

23 - JARO_WINKLER_DISTANCE

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

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)

24 - LEAST

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

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

See also

25 - LEASTB

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

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

See also

26 - LEFT

Returns the specified characters from the left side of a string.

Behavior type

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)

See also

27 - LENGTH

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

Syntax

LENGTH ( expression )

Arguments

expression

String to evaluate, one of the following: CHAR, VARCHAR, BINARY or VARBINARY.

Examples

See also

28 - LOWER

Takes a string value and returns a VARCHAR value converted to lowercase.

Behavior type

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)

29 - LOWERB

Returns a character string with each ASCII character converted to lowercase. Multi-byte characters are skipped and not converted.

Behavior type

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)

30 - LPAD

Returns a VARCHAR value representing a string of a specific length filled on the left with specific characters.

Behavior type

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)

31 - LTRIM

Returns a VARCHAR value representing a string with leading blanks removed from the left side (beginning).

Behavior type

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)

See also

• BTRIM
• RTRIM
• TRIM

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;

33 - MD5

Calculates the MD5 hash of string, returning the result as a VARCHAR string in hexadecimal.

Behavior type

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)

See also

• SHA1

• SHA224

• SHA256

• SHA384

• SHA512

34 - OCTET_LENGTH

Takes one argument as an input and returns the string length in octets for all string types.

Behavior type

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

See also

• BIT_LENGTH
• CHARACTER_LENGTH
• LENGTH

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)

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

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)

37 - POSITION

Returns an INTEGER value representing the character location of a specified substring with a string (counting from one).

Behavior type

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)

38 - POSITIONB

Returns an INTEGER value representing the octet location of a specified substring with a string (counting from one).

Behavior type

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)

39 - QUOTE_IDENT

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.

Behavior type

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)

See also

• QUOTE_LITERAL
• QUOTE_NULLABLE

40 - QUOTE_LITERAL

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

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)

See also

• Character string literals
• QUOTE_NULLABLE

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. Embedded single-quotes and backslashes are properly doubled.

Behavior type

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)

See also

42 - REPEAT

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

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)

43 - REPLACE

Replaces all occurrences of characters in a string with another set of characters.

Behavior type

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 & That', '&', 'and');
    REPLACE
---------------
 This and That
(1 row)

=> SELECT REPLACE('straße', 'ß', 'ss');
 REPLACE
---------
 strasse
(1 row)

44 - RIGHT

Returns the specified characters from the right side of a string.

Behavior type

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)

See also

45 - RPAD

Returns a VARCHAR value representing a string of a specific length filled on the right with specific characters.

Behavior type

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)

46 - RTRIM

Returns a VARCHAR value representing a string with trailing blanks removed from the right side (end).

Behavior type

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)

See also

• BTRIM
• LTRIM
• TRIM

47 - SHA1

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

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)

See also

• MD5

• SHA224

• SHA256

• SHA384

• SHA512

48 - SHA224

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

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)

See also

• MD5

• SHA1()

• SHA256()

• SHA384()

• SHA512()

49 - SHA256

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

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)

See also

• MD5

• SHA1

• SHA224

• SHA384

• SHA512

50 - SHA384

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

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)

See also

• MD5

• SHA1

• SHA224

• SHA256

• SHA512

51 - SHA512

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

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)

See also

• MD5

• SHA1

• SHA224

• SHA256

• SHA384

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

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:

1. Save the first letter. Map all occurrences of a, e, i, o, u, y, h, w to zero (0).

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

3. Replace all adjacent same digits with one digit, and then remove all zero (0) digits

4. If the saved letter's digit is the same as the resulting first digit, remove the digit (keep the letter).

5. Append 3 zeros if result contains less than 3 digits. Remove all except first letter and 3 digits after it.

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

See also

53 - SOUNDEX_MATCHES

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

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)

See also

54 - SPACE

Returns the specified number of blank spaces, typically for insertion into a character string.

Behavior type

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)

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

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)

See also

• SPLIT_PARTB

56 - SPLIT_PARTB

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

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)

See also

• SPLIT_PART

57 - STRPOS

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

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)

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

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)

59 - SUBSTR

Returns VARCHAR or VARBINARY value representing a substring of a specified string.

Behavior type

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)

60 - SUBSTRB

Returns an octet value representing the substring of a specified string.

Behavior type

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.

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

62 - TRANSLATE

Replaces individual characters in string_to_replace with other characters.

Behavior type

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)

63 - TRIM

Combines the BTRIM, LTRIM, and RTRIM functions into a single function.

Behavior type

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)

See also

• BTRIM
• LTRIM
• RTRIM

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

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)

65 - UPPERB

Returns a character string with each ASCII character converted to uppercase. Multibyte characters are not converted and are skipped.

Behavior type

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)