Operator | Expression & Description | Example |
---|---|---|
| | expression1 | expression2 [ | expression ] ... Concatenates two or more expressions. | 'Abcde' | 1 | 23 → Abcde123 |
^@ | string ^@ substring Returns true ( t ) if string starts with substring. This operator is equivalent to the starts_with () function. | 'abcdef' ^@ 'abc' → t |
ascii
input_string
. If the string is empty, it returns NULL
.
bit_length
octet_length
.
btrim
trim (BOTH)
. It removes the specified characters from both the beginning and end of the input string.
char_length
, character_length
, length
chr
concat
concat_ws
convert_from
src_encoding name
. The string must be valid in this encoding.
convert_to
dest_encoding name
and returns a byte array.
decode
base64
, hex
, and escape
.
encode
base64
, hex
, and escape
.
format
sprintf
.
format_string
specifies how the output_string
should be formatted. It consists of text and format specifiers. Text is copied directly to the output string. Format specifiers are placeholders for the arguments to be inserted into the output string. The number of format specifiers should be equal to or less than the number of arguments.
The syntax of the format specifier:
type
is the type of format conversion to use to generate the output of the format specifier.
The allowed values for type
are:
s
: Formats the argument value as a string. NULL is treated as an empty string.I
: Treats the argument value as an SQL identifier.format_string
and format_arg
can be variables.
For example, the following query works fine in RisingWave.
initcap
lower
left
input_integer
characters are removed from the output string.
lpad
ltrim
trim (LEADING)
. It removes the specified characters from the beginning of the input string.
octet_length
overlay
position
quote_literal(string text)
quote_literal
returns null. In such cases, the function quote_nullable is often a better choice. Note that the quotes are part of the output string.
quote_literal(value anyelement)
quote_literal(string text)
. This involves doubling any embedded single-quotes and backslashes to ensure their proper representation.
quote_nullable(string text)
quote_literal
function.
regexp_count
input_string
. Back reference, positive, negative lookahead, and positive, negative lookbehind are supported. Optional flags include i
, which stands for case-insensitive matching, and c
, which represents case-sensitive matching.
regexp_match
i
, which stands for case-insensitive matching, and c
, which represents case-sensitive matching.
regexp_matches
i
, which stands for case-insensitive matching, and c
, which represents case-sensitive matching.
regexp_replace
pattern
in the input_string
, starting from the character index specified by the optional start_integer
.
Back reference, positive, negative lookahead, and positive, negative lookbehind are supported.
Optional flags can modify the matching behavior:
g
flag indicates that all occurrences of the pattern in the input string should be replaced. If not used, only the first occurrence is replaced.i
flag enables case-insensitive matching.c
flag enables case-sensitive matching.start_integer
is used, flags
is not permitted unless N_integer
is also specified.
regexp_split_to_array
repeat
input_string
specific times. Null is returned when times_int
is zero, negative, or null.
replace
from_string
in input_string
with substring to_string
.
reverse
input_string
with its characters in the reverse order.
right
input_integer
characters in the input_string
. If input_integer
is negative, the first |input_integer|
characters are removed from output_string
.
rpad
input_string
on the right with spaces until it reaches the specified length
. If the string is longer than the specified length
, it is truncated to the specified length. Providing the optional padding_string
replaces the spaces with the padding string.
rtrim
trim (TRAILING)
.
split_part
starts_with
substr
/substring
input_string
starting at position start_int
and extending for count_int
characters.
to_ascii
to_hex
input_int
or input_bigint
to its hexadecimal representation as a string.
translate
input_string
that matches a character in the from_string
with the corresponding character in the to_string
.
trim
input_string
that contains only the characters specified in characters (which defaults to whitespace if not specified).
There are two syntax variants.
upper
LIKE
pattern matching expressionsLIKE
expression returns true if the string matches the supplied pattern. The NOT LIKE
expression returns false if LIKE
returns true. By using ILIKE
instead of LIKE
, the matching becomes case-insensitive.
Alternatively, you can use the operators ~~
and ~~*
as equivalents to LIKE
and ILIKE
, respectively. Similarly, the operators !~~
and !~~*
equal to NOT LIKE
and NOT ILIKE
.
_
in a pattern matches any single character.%
matches any sequence of zero or more characters._
or %
, then the pattern only represents the string itself. For example, the pattern ‘apple’ matches only the string ‘apple’. In that case, LIKE
acts like the equals operator =
.
\
. To match the escape character itself, write two escape characters: \\
.
ESCAPE ''
to disable the escape mechanism, but specifying a custom escape character using the ESCAPE
clause is not supported.SIMILAR TO
pattern matching expressionsSIMILAR TO
expression returns true if the string matches the supplied pattern. The NOT SIMILAR TO
expression returns false if SIMILAR TO
returns true. The matching is case-sensitive.
Operator | Description |
---|---|
% | Matches any sequence of zero or more characters. |
_ | Matches any single character. |
| | Denotes alternation (either of two alternatives). |
* | Repeats the previous item zero or more times. |
+ | Repeats the previous item one or more times. |
? | Repeats the previous item zero or one time. |
Repeats the previous item exactly m times. | |
{m,} | Repeats the previous item m or more times. |
{m,n} | Repeats the previous item at least m and not more than n times. |
() | Parentheses group items into a single logical item. |
[…] | A bracket expression specifies a character class. |
\
before the respective character in the pattern. To match the escape character itself, write two escape characters: \\
.
You can use ESCAPE ''
to disable the escape mechanism. The ESCAPE
clause supports specifying a custom escape character, which must be either empty or a single character.