ascii(string) to integer
chr( constant, column, or function, and any combination of arithmetic or string operators.)
char_length,
character_length,
length integer numer of chatracters
octet_length
bit_length constant, column, or function, and any combination of string operators.
SELECT DISTINCT room, length(room)::BIGINT AS length FROM home
concat(str[, …])
concat_ws Concatenates multiple strings together with a specified separator.
concat_ws(separator, str[, …, str_n])
SELECT concat_ws(' -- ', time::STRING, room, temp::STRING) AS concat_ws FROM home LIMIT 3
2024-01-01T08:00:00 -- Kitchen -- 21.0
ends_with ( str , str_test ) if str ends with str_test
SELECT string, ends_with(string, 'USA') AS ends_with FROM
(values ('New York, USA'), ('London, UK'), ('San Francisco, USA')) data(string)
New York, USA true
London, UK false
San Francisco, USA true
find_in_set (str, strlist)
SELECT string, find_in_set(string, 'Isaac,John,Sara') AS find_in_set FROM
(values ('John'), ('Sarah'), ('Isaac')) data(string)
John 2
Sarah 0
Isaac 1
initcap Capitalizes the first character in each word
lower
upper
instr location where a substring first appears in a string (starting at 1). If the substring is not in the string, returns 0.
SELECT string,substr(string, 'neighbor') AS instr FROM
(values ('good neighbor'),
('bad neighbor'),
('next-door neighbor'),
('friend')
) data(string)
good neighbor 6
bad neighbor 5
next-door neighbor 11
friend 0
left (str,n) number of charactersfrom the left
right
levenshtein(str, str2_ Levenshtein distance between
two seuences., i.e. number of single characvter edits to change one word to the other.
SELECT string1, string2,
levenshtein(string1, string2) AS levenshtein
FROM
(values ('kitten', 'sitting'),
('puppy', 'jumping'),
('cow', 'lowing'))
data(string1, string2)
string1 string2 levenshtein
kitten sitting `3
puppy jumping 5
cow lowing 4
lpad(str, n[, padding_str]) default space
rpad
ltrim
rtrim
btrim(str [, trimstr]) trim from the beginning. default white space.
SELECT DISTINCT room, btrim(room::STRING, ' Room') AS btrim FROM home
"Living Room"-> "Living"
md5(str)
overlay(str PLACING substr FROM pos [FOR count])
Replaces part of a string with another substring using a specified starting position and number of characters to replace.
SELECT string,
overlay(string PLACING '****' FROM 1 FOR 12) AS overlay
FROM
(values ('2223000048410010'),
('2222420000001113'),
('4917484589897107')
) data(string)
string overlay
2223000048410010 ****0010
2222420000001113 ****1113
4917484589897107 ****7107
position (substr IN str)
repeat(substr IN str)
replace(str, substr, replacement)
reverse(str)
split_part (str, delimiter, pos)
starts_with((str, substr)
SELECT DISTINCT room, starts_with(room::STRING, 'Kit') AS starts_with FROM home
room starts_with
Kitchen true
Living Room false
int strpos(str, substr)
substr(str, start_pos[, length]) first character str is position 1.
substr_index(str, delimiter, count) substring that occurs before or after
the specified number (count) of delimiter (delimiter) occurrences in a string (str).
If the count is positive, the function returns everything to the left of the final delimiter (counting from the left).
If the count is negative, the function returns everything to the right of the final delimiter (counting from the right).
to_hex(int)
translate(str, chars, translation)
trim(str, chars, translation) Removes leading and trailing spaces from a string.
uuid v4 string value that is unique per row.
SELECT room, uuid() AS uuid FROM (SELECT DISTINCT room FROM home)
|