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

Arguments

• str: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.

Related functions

chr

SELECT DISTINCT
  room,
  ascii(room)::BIGINT AS ascii
FROM home

bit_length

Returns the bit length of a string.

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

length, octet_length

SELECT DISTINCT
  room,
  bit_length(room)::BIGINT AS bit_length
FROM home

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

ltrim, rtrim, trim

SELECT DISTINCT
  room,
  btrim(room::STRING, ' Room') AS btrim
FROM home

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

contcat_ws

SELECT
  concat('At ', time::STRING, ', the ', room, ' was ', temp::STRING, '°C.') AS concat
FROM home
LIMIT 3

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

concat

SELECT
  concat_ws(' -- ', time::STRING, room, temp::STRING) AS concat_ws
FROM home
LIMIT 3

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

ascii

SELECT
  ascii,
  chr(ascii) AS chr
FROM
  (values (112),
          (75),
          (214)
  ) data(ascii)

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

lower, upper

SELECT
  string,
  initcap(string) AS initcap
FROM
  (values ('hello world'),
          ('hello-world'),
          ('hello_world')
  ) data(string)

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

right

SELECT DISTINCT
  room,
  left(room::STRING, 3) AS left
FROM home

length

Returns the number of characters in a string.

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

bit_length, octet_length

SELECT DISTINCT
  room,
  length(room)::BIGINT AS length
FROM home

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

initcap, upper

SELECT DISTINCT
  room,
  lower(room::STRING) AS lower
FROM home

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

rpad

SELECT DISTINCT
  room,
  lpad(room::STRING, 14, '-') AS lpad
FROM home

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

btrim, rtrim, trim

SELECT
  string,
  ltrim(string) AS ltrim
FROM
  (values ('  Leading spaces'),
          ('Trailing spaces  '),
          ('  Leading and trailing spaces  ')
  ) data(string)

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.

SELECT DISTINCT
  room,
  md5(room) AS md5
FROM home

octet_length

Returns the length of a string in bytes.

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

bit_length, length

SELECT DISTINCT
  room,
  octet_length(room)::BIGINT AS octet_length
FROM home

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.

SELECT
  string,
  repeat(string, 3) AS repeat
FROM
  (values ('foo '),
          ('bar '),
          ('baz ')
  ) data(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.

SELECT DISTINCT
  room,
  replace(room::STRING, ' ', '_') AS replace
FROM home

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.

SELECT DISTINCT
  room,
  reverse(room::STRING) AS reverse
FROM home

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

left

SELECT DISTINCT
  room,
  right(room::STRING, 3) AS right
FROM home

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

lpad

SELECT DISTINCT
  room,
  rpad(room::STRING, 14, '-') AS rpad
FROM home

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

btrim, ltrim, trim

SELECT
  string,
  rtrim(string) AS rtrim
FROM
  (values ('  Leading spaces'),
          ('Trailing spaces  '),
          ('  Leading and trailing spaces  ')
  ) data(string)

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.

SELECT
  url,
  split_part(url, '.', 1) AS split_part
FROM
  (values ('www.influxdata.com'),
          ('docs.influxdata.com'),
          ('community.influxdata.com')
  ) data(url)

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.

SELECT DISTINCT
  room,
  starts_with(room::STRING, 'Kit') AS starts_with
FROM home

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

SELECT DISTINCT
  room,
  strpos(room::STRING, 'Room')::BIGINT AS strpos
FROM home

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.

SELECT DISTINCT
  room,
  substr(room::STRING, 3, 5) AS substr
FROM home

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.

SELECT DISTINCT
  room,
  translate(room::STRING, 'Rom', 'sOn') AS translate
FROM home

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.

SELECT
  int,
  to_hex(int) AS to_hex
FROM
  (values (123),
          (345),
          (678)
  ) data(int)

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

btrim, ltrim, rtrim

SELECT
  string,
  trim(string) AS trim
FROM
  (values ('  Leading spaces'),
          ('Trailing spaces  '),
          ('  Leading and trailing spaces  ')
  ) data(string)

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

initcap, lower

SELECT DISTINCT
  room,
  upper(room::STRING) AS upper
FROM home

uuid

Returns a UUID v4 string value that is unique per row.

uuid()

SELECT
  room,
  uuid() AS uuid
FROM (SELECT DISTINCT room FROM home)