SQL 字符串函数

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

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

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

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

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

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

SELECT
  string,
  ends_with(string, 'USA') AS ends_with
FROM
  (values ('New York, USA'),
          ('London, UK'),
          ('San Francisco, USA')
  ) data(string)

SELECT
  string,
  find_in_set(string, 'Isaac,John,Sara') AS find_in_set
FROM
  (values ('John'),
          ('Sarah'),
          ('Isaac')
  ) data(string)

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

SELECT
  string,
  instr(string, 'neighbor') AS instr
FROM
  (values ('good neighbor'),
          ('bad neighbor'),
          ('next-door neighbor'),
          ('friend')
  ) data(string)

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

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

SELECT
  string1,
  string2,
  levenshtein(string1, string2) AS levenshtein
FROM
  (values ('kitten', 'sitting'),
          ('puppy', 'jumping'),
          ('cow', 'lowing')
  ) data(string1, string2)

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

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

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

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

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

SELECT
  string,
  overlay(string PLACING '****' FROM 1 FOR 12) AS overlay
FROM
  (values ('2223000048410010'),
          ('2222420000001113'),
          ('4917484589897107')
  ) data(string)

SELECT
  string,
  position('oo' IN string) AS position
FROM
  (values ('cool'),
          ('scoop'),
          ('ice cream')
  ) data(string)

SELECT
  string,
  repeat(string, 3) AS repeat
FROM
  (values ('foo '),
          ('bar '),
          ('baz ')
  ) data(string)

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

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

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

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

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

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

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

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

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

SELECT
  url,
  substr_index(url, '.', 1) AS subdomain,
  substr_index(url, '.', -1) AS tld
FROM
  (values ('docs.influxdata.com'),
          ('community.influxdata.com'),
          ('cloud2.influxdata.com')
  ) data(url)

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

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

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

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

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