x || y

Concatenation operator

x, y in {string|CLOB}, return type is string

ASCII(x)

Provides ASCII value of the leftmost character in x. If the input is an empty string, the function will return null

return type is integer

CHR(x)

CHAR(x)

Provides the character for ASCII value x ¹

x in {integer}

CONCAT(x, y)

Concatenates x and y with ANSI semantics. If x and/or y is null, returns null

x, y in {string|CLOB}

CONCAT2(x, y)

Concatenates x and y with non-ANSI null semantics. If x and y is null, returns null. If only x or y is null, returns the other value

x, y in {string|CLOB}

INITCAP(x)

Makes the first letter of each word in string x capital and all others lowercase

x in {string}

INSERT(str1, start, length, str2)

Inserts string2 into string1

str1 in {string}, start in {integer}, length in {integer}, str2 in {string}

LCASE(x)

Makes x lowercase

x in {string}

LEFT(x, y)

Gets left y characters of x

x in {string|CLOB}, y in {integer|CLOB}, return type is string

LENGTH(x)

Gets length of x

return type is integer

LOCATE(x, y)

Finds position of x in y starting at the beginning of y

x in {string|CLOB}, y in {string|CLOB}, return type is integer

LOCATE(x, y, z)

Finds position of x in y starting at z

x in {string|CLOB}, y in {string|CLOB}, z in {integer|CLOB}, return type is integer

LPAD(x, y)

Pads input string x with spaces on the left to the length of y

x in {string|CLOB}, y in {integer|CLOB}, return type is string

LPAD(x, y, z)

Pads input string x on the left to the length of y using character z

x in {string|CLOB}, y in {string|CLOB}, z in {character|CLOB}, return type is string

LTRIM(x)

Left trims x of blank chars

x in {string|CLOB}, return type is string

MD5(x)

Calculates the MD5 hash of a string. For more information about supported platforms where the function can be pushed down and further details, please refer to MD5 Function

x in {string}, return type is string

QUERYSTRING(path [, expr [AS name] ...])

Returns a properly encoded query string appended to the given path. Null-valued expressions are omitted, and a null path is treated as ''. Names are optional for column reference expressions, e.g.:

path, expr in {string}, name is an identifier

REPEAT(str1, instances)

Repeats string1 the specified number of times

str1 in {string}, instances in {integer}, return type is string

REPLACE(x, y, z)

Replaces all y in x with z

x,y,z in {string|clob}, return type is string

REGEXP_REPLACE(str, pattern, sub [, flags])

Replaces one or more occurrences of pattern with sub in str ²

str, pattern, sub in {string}, return type is string

RIGHT(x, y)

Gets right y characters of x

x in {string|CLOB}, y in {integer|CLOB}, return type is string

RPAD(input string x, pad length y)

Pads input string x with spaces on the right to the length of y

x in {string|CLOB}, y in {integer|CLOB}, return type is string

RPAD(x, y, z)

Pads input string x on the right to the length of y using character z

x in {string|CLOB}, y in {string|CLOB}, z in {character|CLOB}, return type is string

RTRIM(x)

Right trims x of blank chars

x in {string|CLOB}, return type is string

SPLIT_PART(string, delimiter, position)

Splits a string on the delimiter and returns the given position (counting from one)

string is any type, delimiter is any type, position in {integer, short, byte}, return type is string

SUBSTRING(x, y)

SUBSTRING(x FROM y)

Gets substring from x, from position y to the end of x

y in {integer|CLOB}

SUBSTRING(x, y, z)

SUBSTRING(x FROM y FOR z)

Gets substring from x from position y with length z

y, z in {integer|CLOB}

TO_CHARS(x, encoding)

Returns a CLOB from the BLOB with the given encoding. BASE64, HEX, and the built-in Java Charset names are valid values for the encoding ³

x is a BLOB, encoding is a string, return type is CLOB

TO_BYTES(x, encoding)

Returns a BLOB from the CLOB with the given encoding. BASE64, HEX, and the builtin Java Charset names are valid values for the encoding

x in a CLOB, encoding is a string, return type is BLOB

TRANSLATE(x, y, z)

Translates string x by replacing each character in y with the character in z at the same position

x in {string}

TRIM([[LEADING|TRAILING|BOTH] [x] FROM] y)

Trims the leading, trailing, or both ends of a string y of character x. If LEADING/TRAILING/BOTH is not specified, BOTH is used. If no trim character x is specified, the blank space ' ' is used

x in {character|CLOB}, y in {string|CLOB}

UCASE(x)

Makes x uppercase

x in {string}

UNESCAPE(x)

Unescaped version of x. Possible escape sequences are \b - backspace, \t - tab, \n - line feed, \f - form feed, \r - carriage return. \uXXXX, where X is a hex value, can be used to specify any Unicode character. \XXX, where X is an octal digit, can be used to specify an octal byte value. If any other character appears after an escape character, that character will appear in the output, and the escape character will be ignored

x in {string|CLOB}

URLENCODE(x, encoding)

Converts a string (or a CLOB) to the application/x-www-form-urlencoded MIME format. For more information about HTML form encoding, consult the HTML specification

x in {string |CLOB}, encoding is a string, return type is string (or a CLOB)

URLDECODE(x, encoding)

Decodes a string from the application/x-www-form-urlencoded MIME format

x in {string |CLOB}, encoding is a string, return type is string (or a CLOB)

UUID()

Returns a universally unique identifier. Generates a type 4 (pseudo-randomly generated) UUID using a cryptographically strong random number generator. The format is XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX, where each X is a hex digit

no input value; return type is string

g

global

Replace all occurrences, not just the first

m

multiline

Match over multiple lines

i

case insensitive

Match without case sensitivity