SQL string functions
The InfluxDB SQL implementation supports the following string functions for operating on string values:
- ascii
- bit_length
- btrim
- char_length
- character_length
- concat
- concat_ws
- chr
- initcap
- left
- length
- lower
- lpad
- ltrim
- md5
- octet_length
- repeat
- replace
- reverse
- right
- rpad
- rtrim
- split_part
- starts_with
- strpos
- substr
- to_hex
- translate
- trim
- upper
- uuid
ascii
Returns the ASCII value of the first character in a string.
ascii
returns a 32-bit integer.
To use with InfluxDB, cast the return value to 64-bit integer.
ascii(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
bit_length
Returns the bit length of a string.
bit_length
returns a 32-bit integer.
To use with InfluxDB, cast the return value to 64-bit integer.
bit_length(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
btrim
Trims the specified trim string from the start and end of a string. If no trim string is provided, all whitespace is removed from the start and end of the input string.
btrim(str[, trim_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- trim_str: String expression to trim from the beginning and end of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Default is whitespace characters.
Related functions
char_length
Alias of length.
character_length
Alias of length.
concat
Concatenates multiple strings together.
concat(str[, ..., str_n])
Arguments
- str: String expression to concatenate. Can be a constant, column, or function, and any combination of string operators.
- str_n: Subsequent string expression to concatenate.
Related functions
concat_ws
Concatenates multiple strings together with a specified separator.
concat(separator, str[, ..., str_n])
Arguments
- separator: Separator to insert between concatenated strings.
- str: String expression to concatenate. Can be a constant, column, or function, and any combination of string operators.
- str_n: Subsequent string expression to concatenate. Can be a constant, column, or function, and any combination of string operators.
Related functions
chr
Returns the character with the specified ASCII or Unicode code value.
chr(expression)
Arguments
- expression: Expression containing the ASCII or Unicode code value to operate on. Can be a constant, column, or function, and any combination of arithmetic or string operators.
Related functions
initcap
Capitalizes the first character in each word in the input string. Words are delimited by non-alphanumeric characters.
initcap(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
left
Returns a specified number of characters from the left side of a string.
left(str, n)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- n: Number of characters to return.
Related functions
length
Returns the number of characters in a string.
char_length
returns a 32-bit integer.
To use with InfluxDB, cast the return value to 64-bit integer.
length(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Aliases
- char_length
- character_length
Related functions
lower
Converts a string to lower-case.
lower(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
lpad
Pads the left side of a string with another string to a specified string length.
lpad(str, n[, padding_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- n: String length to pad to.
- padding_str: String expression to pad with. Can be a constant, column, or function, and any combination of string operators. Default is a space.
Related functions
ltrim
Removes leading spaces from a string.
ltrim(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
md5
Computes an MD5 128-bit checksum for a string expression.
md5(str)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
octet_length
Returns the length of a string in bytes.
length
returns a 32-bit integer.
To use with InfluxDB, cast the return value to 64-bit integer.
octet_length(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
repeat
Returns a string with an input string repeated a specified number.
repeat(str, n)
Arguments
- str: String expression to repeat. Can be a constant, column, or function, and any combination of string operators.
- n: Number of times to repeat the input string.
replace
Replaces all occurrences of a specified substring in a string with a new substring.
replace(str, substr, replacement)
Arguments
- str: String expression to repeat. Can be a constant, column, or function, and any combination of string operators.
- substr: Substring expression to replace in the input string. Can be a constant, column, or function, and any combination of string operators.
- replacement: Replacement substring expression. Can be a constant, column, or function, and any combination of string operators.
reverse
Reverses the character order of a string.
reverse(str)
Arguments
- str: String expression to repeat. Can be a constant, column, or function, and any combination of string operators.
right
Returns a specified number of characters from the right side of a string.
right(str, n)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- n: Number of characters to return.
Related functions
rpad
Pads the right side of a string with another string to a specified string length.
rpad(str, n[, padding_str])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- n: String length to pad to.
- padding_str: String expression to pad with. Can be a constant, column, or function, and any combination of string operators. Default is a space.
Related functions
rtrim
Removes trailing spaces from a string.
rtrim(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
split_part
Splits a string based on a specified delimiter and returns the substring in the specified position.
split_part(str, delimiter, pos)
Arguments
- str: String expression to spit. Can be a constant, column, or function, and any combination of string operators.
- delimiter: String or character to split on.
- pos: Position of the part to return.
starts_with
Tests if a string starts with a substring.
starts_with(str, substr)
Arguments
- str: String expression to test. Can be a constant, column, or function, and any combination of string operators.
- substr: Substring to test for.
strpos
Returns the starting position of a specified substring in a string. Positions begin at 1. If the substring does not exist in the string, the function returns 0.
strpos
returns a 32-bit integer.
To use with InfluxDB, cast the return value to 64-bit integer.
strpos(str, substr)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- substr: Substring expression to search for. Can be a constant, column, or function, and any combination of string operators.
substr
Extracts a substring of a specified number of characters from a specific starting position in a string.
substr(str, start_pos[, length])
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- start_pos: Character position to start the substring at. The first character in the string has a position of 1.
- length: Number of characters to extract. If not specified, returns the rest of the string after the start position.
translate
Translates characters in a string to specified translation characters.
translate(str, chars, translation)
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- chars: Characters to translate.
- translation: Translation characters. Translation characters replace only characters at the same position in the chars string.
to_hex
Converts an integer to a hexadecimal string.
to_hex(int)
Arguments
- int: Integer expression to convert. Can be a constant, column, or function, and any combination of arithmetic operators.
trim
Removes leading and trailing spaces from a string.
trim(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
upper
Converts a string to upper-case.
upper(str)
Arguments
- str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
Related functions
uuid
Returns a UUID v4 string value that is unique per row.
uuid()
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.