String Functions

String functions are called so because they typically take strings as inputs and return strings as outputs. But this is not always the case: some functions may take as input or return an integer, so please check the data type constraints carefully.

Unless specified otherwise, all indexes are 1-based. The 0 index is considered to be before the start of the string.

To view the full table, click the expand button in its top right corner

Function Definition Data Type Constraint

x || y

Concatenation operator

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

ASCII(x) Provide ASCII value of the leftmost character in x. If the input is an empty string, the function will return null return type is an integer

CHR(x)

CHAR(x)

Provide the character for ASCII value x¹

x in {integer}

CONCAT(x, y)

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

x, y in {string}

CONCAT2(x, y)

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

x, y in {string}

INITCAP(x)

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

x in {string}

INSERT(str1, start, length, str2)

Insert string2 into string1

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

LCASE(x)

Make x lowercase

x in {string}

LEFT(x, y)

Get left y characters of x

x in {string}, y in {integer}, return string

LENGTH(x)

Get length of x

return type is an integer

LOCATE(x, y)

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

x in {string}, y in {string}, return integer

LOCATE(x, y, z)

Find the position of x in y starting at z

x in {string}, y in {string}, z in {integer}, return integer

LPAD(x, y)

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

x in {string}, y in {integer}, return string

LPAD(x, y, z)

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

x in {string}, y in {string}, z in {character}, return string

LTRIM(x)

Left trim x of blank chars

x in {string}, return string

MD5(x)

Calculate 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}, returns a 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.:

QUERYSTRING('path', 'value' as "&x", ' & ' as y, null as z) returns 'path?%26x=value&y=%20%26%20'

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

REPEAT(str1, instances)

Repeat string1 the specified number of times

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

REPLACE(x, y, z)

Replace all y in x with z

x,y,z in {string}, return string

REGEXP_REPLACE(str, pattern, sub [, flags]) Replace one or more occurrences of pattern with sub in str² str, pattern, sub in {string}, return string

RIGHT(x, y)

Get right y characters of x

x in {string}, y in {integer}, return string

RPAD(input string x, pad length y)

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

x in {string}, y in {integer}, return string

RPAD(x, y, z)

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

x in {string}, y in {string}, z in {character}, return string

RTRIM(x)

Right trim x of blank chars

x is a string, return a string

SPLIT_PART(string, delimiter, position) Split a string on the delimiter and return the given position (counting from one) string is any type, delimiter is any type, position in {integer, short, byte}, returns a string

SUBSTRING(x, y)

SUBSTRING(x FROM y)

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

y in {integer}

SUBSTRING(x, y, z)

SUBSTRING(x FROM y FOR z)

Get substring from x from position y with length z

y, z in {integer}

TO_CHARS(x, encoding)

Return 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, and returns a clob

TO_BYTES(x, encoding)

Return 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, and returns a blob

TRANSLATE(x, y, z)

Translate 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)

Trim 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}, y in {string}

UCASE(x)

Make 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}

URLENCODE(x, encoding)

Convert 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

URLENCODE('http://The string ü@foo-bar', 'UTF-8')

x in {string | clob}, encoding is a string, returns a string (or a clob)

URLDECODE(x, encoding)

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

URLDECODE('The+string+%C3%BC%40foo-bar', 'UTF-8')

x in {string | clob}, encoding is a string, returns a 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; returns a string

Special Note on CHR(x) CHAR(x)

Non-ASCII range characters or integers used in these functions may produce different results or exceptions depending on where the function is evaluated (the Data Virtuality Server vs source). The Data Virtuality Server uses Java-default int to char and char to int conversions, which operate over UTF16 values.

Special Note on REGEXP_REPLACE(str, pattern, sub [, flags])

The pattern parameter is expected to be a valid Java Regular Expression.

The flags argument can be any concatenation of any of the valid flags with the following meanings:

Flag	Name	Meaning
g	global	Replace all occurrences, not just the first
m	multiline	Match over multiple lines
i	case insensitive	Match without case sensitivity

Special Note on TO_CHARS(x, encoding)

Please consult the Class Charset section of Java documentation for more on supported Charset names. For charsets, un-mappable chars will be replaced with the charset default character. Conversion of binary formats, such as BASE64, to bytes, will result in an error as an unrecognizable character is encountered.