Skip to main content

String Functions and Operators

String functions and operators process string inputs and return string or numeric values. The string types supported by Extreme DPS are varchar and text. For more information about the string types, see Character Types.

SQL string functions and operators​

This section provides details about each SQL string functions and operators supported by Extreme DPS.

||

Concatenates one or more strings.

info

Null values will be ignored.

Syntax​

<string1> || <string2>[, ... <string n>]

Arguments​

<string n>: a varchar string to concatenate.

Returns​

A varchar value.

Examples​

>SELECT 'Extreme' || 'DPS';
ExtremeDPS

BIT_LENGTH​

Returns the length of a string in bits.

Syntax​

BIT_LENGTH(<string>)

Arguments​

<string>: the varchar string of which the length is to be evaluated.

Returns​

An integer value.

Examples​

>SELECT BIT_LENGTH('Extreme DPS');
88

CHAR_LENGTH or CHARACTER_LENGTH​

Returns the number of characters in a string.

info

CHARACTER_LENGTH is an alias for CHAR_LENGTH.

Syntax​

CHAR_LENGTH(<string>)
CHARACTER_LENGTH(<string>)

Arguments​

<string>: the string of which the length is to be evaluated.

Returns​

A integer value.

Examples​

>SELECT CHAR_LENGTH('Extreme DPS');
11

CONCAT_WS​

Concatenates strings with a separator.

The first argument specified in the function is the separator. Null values will be ignored.

Syntax​

CONCAT_WS( <separator>, <string1>[, <string2> ...])

Arguments​

  • <separator>: a text string that functions as the separator.

  • <string>: a text string to concatenate.

Returns​

A text value.

Examples​

>SELECT CONCAT_WS(':', 'adbcd', 'efg', 'hijk');
'adbcd:efg:hijk'

LOWER​

Converts all characters in a string to lowercase.

Syntax​

LOWER(<string>)

Arguments​

<string>: the varchar string to convert.

Returns​

A varchar value.

Examples​

>SELECT LOWER('Extreme DPS');
extreme dps

OCTET_LENGTH​

Returns the length of a string in bytes.

Syntax​

OCTET_LENGTH(<string>)

Arguments​

<string>: the varchar string of which the length is to be evaluated.

Returns​

An integer value.

Examples​

>SELECT OCTET_LENGTH('Extreme DPS');
11

POSITION​

Returns the position of a substring in a string when the substring occurs in the string for the first time.

The position of the first character in the string is 1.

Syntax​

POSITION(<substring> in <string>)

Arguments​

  • <substring>: the varchar string to search for.

  • <string>: the varchar string to search.

Returns​

An integer value.

Examples​

>SELECT POSITION('dps' in 'extremedps');
8

SUBSTRING or SUBSTR​

Returns the substring that starts from the position with the number of characters if specified.

The position in the string is 1.

Syntax​

SUBSTRING(<string>[ from <position>] [for <length>] )
SUBSTR(<string>[ from <position>] [for <length>] )
SUBSTR(<string>, <position> [, <length>])

Note that SUBSTRING(<string> for <length>) or SUBSTR(<string> for <length>) is not supported.

Arguments​

  • <string>: the varchar string to search.
  • <position>: an integer expression that specifies the offset from which the substring starts.
  • <length>: an integer expression that specifies the length of the substring.

Returns​

A varchar value.

If any of the argument is null, null will be returned.

Examples​

>SELECT SUBSTRING('Extreme DPS' from 3 for 3);
tre

>SELECT SUBSTRING('Extreme DPS' from 3);
treme DPS

>SELECT SUBSTR('null' from 3);
null

> SELECT SUBSTR('alphabet', 3, 2);
ph

UPPER​

Converts all characters in a string to uppercase.

Syntax​

UPPER(<string>)

Arguments​

  • <string>: the varchar string to convert.

Returns​

A varchar value.

Examples​

>SELECT UPPER('ExtremeDPS');
EXTREMEDPS

Other string functions​

Extreme DPS also supports many string manipulation functions. Some of them are used internally to implement the SQL string functions described in the previous section.

This section provides details about each SQL string functions and operators supported by Extreme DPS.

ASCII​

Returns the ASCII code for the first character of the string, or the Unicode code point of the character if UTF8 is used.

Syntax​

ASCII(<string>)

Arguments​

<string>: the string of which the ASCII code for the first character will be returned.

Returns​

An integer value.

If <string> is null, null will be returned.

Examples​

>SELECT ASCII('xyz');
120

BTRIM​

Returns the given string with the leading and trailing characters removed.

Syntax​

BTRIM(<string> [, <trimStr>])

Arguments​

  • <string>: the varchar string to be trimmed.
  • <trimStr>: the characters to remove from both the left and right sides of <string>. The default value of <trimStr> is ' '. This indicates that the leading and trailing white spaces will be removed if <trimStr> is not specified.

Returns​

A varchar value.

If <string> is null, null will be returned.

Examples​

>SELECT BTRIM('xyxtrimyyx', 'xyz');
trim

>SELECT BTRIM('xyxtrimyyx  ', ' ');
xyxtrimyyx

CHR​

Converts a Unicode code point into the character that matches the input Unicode.

For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate an ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes.

Syntax​

CHR(<integer>)

Arguments​

<integer>: the integer expression to convert.

Returns​

A varchar value.

If integer is null, null will be returned.

Examples​

>SELECT CHR(65);
A

CONCAT​

Concatenates one or more strings.

Syntax​

CONCAT(<string1>[, ... <string n>])

Arguments​

<string n>: a string of any data type to concatenate.

Returns​

A varchar value.

If any string is null, it will be ignored.

Examples​

>SELECT CONCAT('abcde', 2, NULL, 22);
abcde222

INITCAP​

Returns the input string with the first letter of each word uppercase and the rest lowercase.

Syntax​

INITCAP(<string>)

Arguments​

<string>: the string to convert.

Returns​

A varchar value.

If any string is null, it will be ignored.

Examples​

>SELECT INITCAP('welcome to relyt');
Welcome To Relyt

LEFT​

Returns the substring with the specified number of leftmost characters in a string.

Syntax​

LEFT(<string>, <length>)

Arguments​

  • <string>: the text expression to search.

  • <length>: the integer expression that specifies the length of the substring to return.

    If <length> is a positive integer, the leftmost <length> characters will be returned. If <length> is negative, all of the characters in the string except the rightmost -length characters will be returned. If *<length>* is 0`, null will be returned.

Returns​

A varchar value.

Examples​

>SELECT LEFT('abcde', 2);
ab

LENGTH​

Returns the number of characters in the string.

Syntax​

LENGTH(<string>)

Arguments​

<string>: the text or varchar expression of which the number of characters will be count.

Returns​

An integer value.

Examples​

>SELECT LENGTH('extremedps');
10

LPAD​

Left-pads the specified characters to a string to make the string consist of the specified number of characters. If the string is already longer than the specified length, the string will be truncated.

Syntax​

LPAD(<string>, <length> [, <pad>])

Arguments​

  • <string>: the text string to be padded.

  • <length>: the integer expression specifying the number of characters that <string> will consist of after padding.

  • <pad>: the text expression to pad the <string>.

    The default value of <pad> is white spaces. This indicates that white spaces will be padded to the <string> if <pad> is not specified.

Returns​

A varchar value.

Examples​

>SELECT LPAD('hi', 5, 'xy');
xyxhi

LTRIM​

Returns a string with the leading characters removed.

Syntax​

LTRIM(<string>[, <trimStr>])

Arguments​

  • <string>: the text string to be trimmed.

  • <trimStr>: the text substring. The leading characters that are within this substring will be removed from the <string>.

    The default value of <trimStr> is ' '. This indicates that the leading white spaces will be removed if <trimStr> is not specified.

Returns​

A varchar value.

Examples​

>SELECT LTRIM('zzzytest', 'xyz');
test

QUOTE_LITERAL​

Encloses a string in single quotation marks so that the quoted string can be used as a literal string in an SQL statement string. Embedded single-quotes (') and backslashes () are properly doubled.

Syntax​

QUOTE_LITERAL(<string>)

Arguments​

<string>: the text string to be quoted in single quotation marks.

Returns​

A varchar value.

If <string> is null, null will be returned.

Examples​

>SELECT QUOTE_LITERAL(E'O\'Reilly');
'O''Reilly'

QUOTE_NULLABLE​

Coerces the given value to text and then quote it as a literal. Embedded single-quotes (') and backslashes () are properly doubled.

Syntax​

QUOTE_NULLABLE(<string>)

Arguments​

<string>: the text string to be quoted.

Returns​

A varchar value.

Examples​

>SELECT QUOTE_NULLABLE(NULL);
NULL

REPEAT​

Repeats a string for the specified times.

Syntax​

REPEAT(<string>, <number>)

Arguments​

  • <string>: the text string to be repeated.
  • <number>: the integer expression specifying the number of times for which the <string> will be repeated.

Returns​

A varchar value.

Examples​

>SELECT REPEAT('DPS', 3);
DPSDPSDPS

REPLACE​

Replaces all occurrences of one substring to another in a string.

Syntax​

REPLACE(<string>, <sourceStr>, <targetStr>)

Arguments​

  • <string>: the text string.
  • <sourceStr>: the text substring that will be replaced.
  • <targetStr>: the text substring used to replace the sourceStr.

Returns​

A varchar value.

Examples​

>SELECT REPLACE('abcdefabcdef', 'cd', 'XX');
abXXefabXXef

REVERSE​

Reverses the order of characters in a string.

Syntax​

REVERSE(<string>)

Arguments​

<string>: the text or varchar string to be reversed.

Returns​

A varchar value.

Examples​

>SELECT REVERSE('abcde');
edcba

Returns the substring with the specified number of rightmost characters in a string.

Syntax​

RIGHT(<string>, <length>)

Arguments​

  • <string>: the text expression to search.

  • <length>: the integer expression that specifies the length of the substring to return.

    If <length> is a positive integer, the rightmost <length> characters will be returned. If <length> is negative, all of the characters in the string except the leftmost -length characters will be returned. If <length> is 0, null will be returned.

Returns​

A varchar value.

Examples​

>SELECT RIGHT('abcde', 2);
de

RPAD​

Right-pads the specified characters to a string to make the string consist of the specified number of characters. If the string is already longer than the specified length, the string will be truncated.

Syntax​

RPAD(<string>, <length> [, <pad>])

Arguments​

  • <string>: the text string to be padded.

  • <length>: the integer expression specifying the number of characters that <string> will consist of after padding.

  • <pad>: the text expression to pad the <string>.

    The default value of <pad> is white spaces. This indicates that white spaces will be padded to the <string> if <pad> is not specified.

Returns​

A varchar value.

Examples​

>SELECT RPAD('hi', 5, 'xy');
hixyx

RTRIM​

Returns a string with the tailing characters removed.

Syntax​

RTRIM(<string>[, <trimStr>])

Arguments​

  • <string>: the text string to be trimmed.

  • <trimStr>: the text substring. The tailing characters that are within this substring will be removed from the <string>.

    The default value of <trimStr> is ' '. This indicates that the trailing white spaces will be removed if <trimStr> is not specified.

Returns​

A varchar value.

Examples​

>SELECT RTRIM('testxxzx', 'xyz');
test

SPLIT_PART​

Splits a string at the occurrences of the specified delimiter and returns the requested part.

Syntax​

SPLIT_PART(<string>, <delimiter>, <partNum>)

Arguments​

  • <string>: the text string to be split.
  • <delimiter>: the text expression that functions as the delimiter to split the <string>.
  • <partNum>: the integer expression specifying which part will be returned.

Returns​

A varchar value.

If <partNum> is greater than or equal to 1, the partNumth part will be returned. If <partNum> is 0, the first part will be returned, which gets the same result as 1.

Examples​

>SELECT SPLIT_PART('abc~@~def~@~ghi', '~@~', 2);
def

STRPOS​

Returns the position of a substring in a string when the substring occurs the first time.

This function is an equivalent to POSITION(varchar in varchar).

Syntax​

STRPOS(<string>, <substring>)

Arguments​

  • <string>: the text string to search.
  • <substring>: the text substring to search for.

Returns​

An integer value.

Examples​

>SELECT STRPOS('high', 'ig');
2

STARTS_WITH​

Returns true if a string starts with the specified prefix.

Syntax​

STARTS_WITH(<string>, <prefix>)

Arguments​

  • <string>: the text string.
  • <prefix>: the text expression specifying the prefix.

Returns​

A boolean value.

Examples​

>SELECT STARTS_WITH('alphabet', 'alph');
t