Cette page n'est pas encore disponible en français, sa traduction est en cours. Si vous avez des questions ou des retours sur notre projet de traduction actuel, n'hésitez pas à nous contacter.
Returns a substring of s from start to a max of numChars, if provided. start is a 1-based index, so substr('hello', 2) returns 'ello'. If the start is less than 1, it is treated as if it were 1. The result is computed by taking the range of characters [start, start+numChars], where if any value is less than 1, it is treated as 1. This means substr('hello', -2, 4) returns 'h'.
replace(text s, text from, text to)
text
Replaces all occurrences in s of substring from with substring to.
regexp_replace(text s, text pattern, text replacement)
text
Replace substrings in s that match the POSIX regular expression pattern with the replacement. Supports Go’s regular expression syntax.
reverse(expr text)
string
Reverses the string (brown → nworb).
md5(expr text)
string
Calculates the MD5 hash of a string and returns the result in hexadecimal.
char_length(str text)
integer
Returns number of characters in str.
left(str text, n int)
text
Returns first n characters in str. When n is negative, return all but last |n| characters.
right(str text, n int)
text
Returns last n characters in str. When n is negative, return all but first |n| characters.
ltrim(str text [, characters text])
text
Removes the longest string containing only characters from characters (a space by default) from the start of str.
rtrim(str text [, characters text])
text
Removes the longest string containing only characters from characters (a space by default) from the end of str
trim([leading | trailing | both] [characters] from str)
text
Removes the longest string containing only the characters (a space by default) from the start/end/both ends of str.
sort_order_ip(ip text)
text
Returns a string representing a sort order over IPv4 and IPv6 range.
Returns true if the value the expr e evaluates to is in the array a for each row.
array_cat(array a, array b)
array
Returns a new array containing the combined elements from array a and array b.
array_append(array a, expr e)
array
Returns a new array that includes all the original elements of the input array followed by the appended element.
string_to_array(text s, delimiter, [,nullString])
array
Returns an array of substrings obtained by splitting the input string s, using the specified delimiter. The third argument, nullString, is optional and specifies substrings that are replaced with NULL.
array_to_string(array a, delimiter, [,nullString])
string
Concatenates array elements using supplied delimiter and optional null string.
unnest(array a)
variable
Returns each element in the array as a separate row. The return type is the element type of the array. unnest can only be used in the SELECT clause of a query. If other columns are SELECTed with unnest, the value at each row in the table is repeated at each output row with each unnested element. If multiple columns are being unnested, all the unnested columns are zipped up together, with NULL filling in the output values for shorter arrays.
Extracts the JSON sub-object in JSON as text, defined by the path. Its behavior is equivalent to the postgres function with the same name. For example, json_extract_path_text(col, ‘forest') returns the value of the key forest for each JSON object in col. See the example below for a JSON array syntax.
json_extract_path(text json, text path…)
json
Same functionality as json_extract_path_text, but returns a column of JSON type instead of text type.
Builds a JSON object based on the parameters passed in. The parameters to the function are the keys/values of the JSON object being built, alternating between key and value mapped to each key.
row_to_json(table)
json
Returns a JSON representation of each row in a table as a JSON value. The JSON keys are the column names, and the values are the values under each row at each column.
Note: row_to_json takes in a table name, NOT a column, for example, SELECT row_to_json(<table>) FROM <table>.
