UnityJDBC - String Functions

Summary

Function Syntax Description
ASCII ASCII(expr) Returns the ascii number of the leftmost character in expr.
BIT_LENGTH BIT_LENGTH(expr) Returns the number of bits in expr.
CHAR CHAR(expr) Returns a character that has the ASCII code of (expr).
CHAR_LENGTH CHAR_LENGTH (expr) Returns the number of characters in (expr).
CONCAT CONCAT(expr1, expr2) Appends two or more literal expressions into one string.
CONCATENATE CONCATENATE(expr1, expr2) Appends two or more literal expressions into one string.
INSTR INSTR(str, substr) Returns the position of the first occurrence of str in substr.
INSTR INSTR(str, substr, pos) Returns the position of the first occurrence of substring substr in string str, starting at position pos.
LCASE LCASE(expr) Coverts expr into lower case.
LEFT LEFT(expr, len) Returns the leftmost len characters from the string expr.
LEN LEN (expr) Returns the number of characters in (expr).
LENGTH LENGTH (expr) Returns the number of characters in expr.
LOWER LOWER(expr) Coverts expr into lower case.
LTRIM LTRIM(expr) Removes whitespace at the beginning of the string.
NVL NVL(expr1, expr2) If expr1 is an empty string or NULL, replaces with expr2.
OCTET_LENGTH OCTET_LENGTH(expr) Returns the number of bytes (octets) in expr.
POSITION POSITION(str, substr) Returns the position of the first occurrence of str in substr.
REGEXP_LIKE REGEXP_LIKE(str, regexp) Returns true if string str matches regular expression str in regesp.
REPLACE REPLACE(str, oldstr, newstr) Replaces all occurences of oldstr by newstr in a string str.
REVERSE REVERSE(str) Reverse the string str.
RIGHT RIGHT(expr, len) Returns the rightmost len characters from the string expr.
RTRIM RTRIM(expr) Removes whitespace at the end of the string.
SPACE SPACE(expr) Returns a string consisting of expr space characters.
STUFF STUFF(str, pos, len, newstr) Returns the string str with the substring beginning at position pos and len characters long replaced by the string newstr.
SUBSTRING SUBSTRING(str, start, len) Extracts from str starting at pos len characters.
SUBSTRING SUBSTRING(str, len) Extracts from str starting at the first character len characters.
TRIM TRIM(expr, type, trimchars) Removes leading or trailing characters of given type.
TRIM TRIM(expr) Removes leading characters and trailing characters from a string.
UCASE UCASE(expr) Coverts expr into upper case.
UPPER UPPER(expr) Coverts expr into upper case.

Detailed Function Descriptions

ASCII

Syntax: ASCII(expr)

Returns the ascii number of the leftmost character in expr.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

BIT_LENGTH

Syntax: BIT_LENGTH(expr)

Returns the number of bits in expr.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

CHAR

Syntax: CHAR(expr)

Returns a character that has the ASCII code of expr.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

CHAR_LENGTH

Syntax: CHAR_LENGTH (expr)

Returns the number of characters in expr. Equivalent to LEN() and LENGTH().

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

CONCAT

Syntax: CONCAT(expr1, expr2)

Appends two or more literal expressions into one string. Equivalent to CONCATENATE() or using the + or || operators.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

CONCATENATE

Syntax: CONCATENATE(expr1, expr2)

Appends two or more literal expressions into one string. Equivalent to CONCAT() or using the + or || operators.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

INSTR

Syntax: INSTR(str, substr)

Returns the position of the first occurrence of str in substr.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

INSTR

Syntax: INSTR(str, substr, pos)

Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

LCASE

Syntax: LCASE(expr)

Converts a string to lower case. Equivalent to LOWER().

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

LEFT

Syntax: LEFT(expr, len)

Returns the leftmost len characters from the string expr or NULL if any argument is NULL.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

LEN

Syntax: LEN (expr)

Returns the length of a string in characters. Equivalent to CHAR_LENGTH() and LENGTH().

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

LENGTH

Syntax: LENGTH (expr)

Returns the length of a string in characters. Equivalent to CHAR_LENGTH() and LEN().

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

LOWER

Syntax: LOWER(expr)

Converts a string to lower case. Equivalent to LCASE().

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

LTRIM

Syntax: LTRIM(expr)

Removes whitespace at the beginning of the string.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

NVL

Syntax: NVL(expr1, expr2)

If expr1 is an empty string or NULL, replaces with expr2.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

OCTET_LENGTH

Syntax: OCTET_LENGTH(expr)

Returns an integer value representing the number of octets in an expression. This value is the same as BIT_LENGTH/8.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

POSITION

Syntax: POSITION(str, substr)

Returns an integer value representing the starting position of a string within the search string. Note that UnityJDBC does not support the SQL standard syntax of POSITON(?2 in ?1).

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

REGEXP_LIKE

Syntax: REGEXP_LIKE(str, regexp)

Returns true if string str matches regular expression str in regesp.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

REPLACE

Syntax: REPLACE(str, oldstr, newstr)

Replaces all occurences of oldstr by newstr in a string str.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

REVERSE

Syntax: REVERSE(str)

Reverse the string str.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

RIGHT

Syntax: RIGHT(expr, len)

Returns the rightmost len characters from the string expr or NULL if any argument is NULL.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

RTRIM

Syntax: RTRIM(expr)

Removes whitespace at the end of the string.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

SPACE

Syntax: SPACE(expr)

Returns a string consisting of expr space characters.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

STUFF

Syntax: STUFF(str, pos, len, newstr)

Returns the string str with the substring beginning at position pos and len characters long replaced by the string newstr.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

SUBSTRING

Syntax: SUBSTRING(str, start, len)

Extracts from str starting at pos len characters.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

SUBSTRING

Syntax: SUBSTRING(str, len)

Extracts from str starting at the first character len characters.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

TRIM

Syntax: TRIM(expr, type, trimchars)

Removes leading characters, trailing characters, or both from a character string. First parameter is source string. Second parameter is type: 'BOTH', "LEADING', 'TRAILING'. Third parameter is string of characters that are trimmed. Example: 'ab ' would remove a, b, and spaces.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

TRIM

Syntax: TRIM(expr)

Removes leading characters and trailing characters from a string.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

UCASE

Syntax: UCASE(expr)

Converts a string to upper case. Equivalent to UPPER().

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

UPPER

Syntax: UPPER(expr)

Converts a string to upper case. Equivalent to UCASE().

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB