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 | |
---|---|---|---|
| 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 | |
| Provide the character for ASCII value x 1 | x in {integer} | |
| Concatenate x and y with ANSI semantics. If x and/or y is null, returns null | x, y in {string} | |
| 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} | |
| Make the first letter of each word in string x capital and all others lowercase | x in {string} | |
| Insert string2 into string1 | str1 in {string}, start in {integer}, length in {integer}, str2 in {string} | |
| Make x lowercase | x in {string} | |
| Get left y characters of x | x in {string}, y in {integer}, return string | |
| Get length of x | return type is an integer | |
| Find the position of x in y starting at the beginning of y | x in {string}, y in {string}, return integer | |
| Find the position of x in y starting at z | x in {string}, y in {string}, z in {integer}, return integer | |
| Pad input string x with spaces on the left to the length of y | x in {string}, y in {integer}, return string | |
| 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 | |
| Left trim x of blank chars | x in {string}, return string | |
| 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.:
| path, expr in {string}. name is an identifier | |
| Repeat string1 the specified number of times | str1 in {string}, instances in {integer} return string | |
| 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 2 | str, pattern, sub in {string}, return string | |
| Get right y characters of x | x in {string}, y in {integer}, return string | |
| Pad input string x with spaces on the right to the length of y | x in {string}, y in {integer}, return string | |
| 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 | |
| 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 | |
| Get substring from x, from position y to the end of x | y in {integer} | |
| Get substring from x from position y with length z | y, z in {integer} | |
| 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 3 | x is a blob, encoding is a string, and returns a clob | |
| 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 string x by replacing each character in y with the character in z at the same position | x in {string} | |
| 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} | |
| Make x uppercase | x in {string} | |
| 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
| x in {string | clob}, encoding is a string, returns a string (or a clob) | |
URLDECODE(x, encoding) | Decode a string from the
| 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.