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

ascii

Returns the ASCII value of the first character in a string.

ascii(str)

Arguments

• str: String column or literal string to operate on.

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 column or literal string to operate on.

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 column or literal string to operate on.
• trim_str: String column or literal string to trim from the beginning and end of the input string. 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 column or literal string to concatenate.
• str_n: Subsequent string column or literal string 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 column or literal string to concatenate.
• str_n: Subsequent string column or literal string to concatenate.

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

chr(acsii)

Arguments

• ascii: ASCII code value to operate on.

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 column or literal string to operate on.

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 column or literal string to operate on.
• 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 column or literal string to operate on.

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 column or literal string to operate on.

Related functions

initcap, upper

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

lpad

Pads the left side a string with another string to a specified string length.

lpad(str, n[, padding_str])

Arguments

• str: String column or literal string to operate on.
• n: String length to pad to.
• padding_str: String column or literal string to pad with. 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 column or literal string to operate on.

Related functions

btrim, rtrim, trim

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

md5

Calculates and returns an MD5 128-bit checksum for a string.

md5(str)

Arguments

• str: String column or literal string to operate on.

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 column or literal string to operate on.

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 column or literal string to repeat.
• 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 column or literal string to repeat.
• substr: Substring to replace in the input string.
• replacement: Replacement substring.

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

reverse

Reverses the character order of a string.

reverse(str)

Arguments

• str: String column or literal string to repeat.

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 column or literal string to operate on.
• n: Number of characters to return.

Related functions

left

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

rpad

right side a string with another string to a specified string length.

rpad(str, n[, padding_str])

Arguments

• str: String column or literal string to operate on.
• n: String length to pad to.
• padding_str: String column or literal string to pad with. 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 column or literal string to operate on.

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 a the specified position.

split_part(str, delimiter, pos)

Arguments

• str: String column or literal string to spit.
• 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 column or literal string to test.
• 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 column or literal string to operate on.
• substr: Substring to search for.

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 column or literal string to operate on.
• 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 column or literal string to operate on.
• 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 column or literal integer to convert.

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 column or literal string to operate on.

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 column or literal string to operate on.

Related functions

initcap, lower

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