String Functions

You are looking at an older version of the documentation. The latest version is found here.

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|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.:

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)

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

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

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

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

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

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.

Encoding a String Using TO_CHARS

If you need to encode a string in Base64, you can use this snippet:

SQL

SELECT to_chars(to_bytes('Plaintext string', 'UTF-8'), 'base64' );;