String functions

TimesTen supports these string functions in SELECT statements:

SUBSTR
INSTR
LENGTH

A selected value that specifies a string function causes the SELECT result to be materialized. This causes overhead in both time and space.

SUBSTR

Returns a CHAR, VARCHAR2 or NVARCHAR2 that represents a substring of a CHAR or NCHAR string. The returned substring is of a specified number of characters, beginning from a designated starting point, relative to either the beginning or end of the string.

SQL syntax

{SUBSTR | SUBSTRB | SUBSTR4}=(char, m, n)

Parameters

SUBSTR has the parameters:

Parameter	Description
`char`	The string for which this function returns a substring If `char` is a CHAR string, the result is a CHAR or VARCHAR2 string. If `char` is a NCHAR string, the result is a NVARCHAR2 string.
`m`	The position at which to begin the substring. If `m` is positive, the first character of the returned string is `m` characters from the beginning of the string specified in `char`. Otherwise it is `m` characters from the end of the string. If ABS(`m`) is bigger than the length of the character string, a NULL value is returned.
`n`	The number of characters to be included in the substring. If `n` is omitted, all characters to the end of the string specified in `char` are returned. If `n` is less than 1 or if `char`, `m` or `n` is NULL, NULL is returned.

Description

SUBSTR calculates lengths using characters as defined by character set. SUBSTRB uses bytes instead of characters. SUBSTR4 uses UCS4 code points.

Examples

In the first 5 rows of employees, select the first three characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,1,3) FROM employees;
< Kin >
< Koc >
< De  >
< Hun >
< Ern >
5 rows found.

In the first 5 rows of employees, select the last five characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,-5,5) FROM employees;
< <NULL> >
< chhar >
<  Haan >
< unold >
< Ernst >
5 rows found.

INSTR

Determines the first position, if any, at which one string occurs within another. If the substring does not occur in the string, then 0 is returned. The position returned is always relative to the beginning of CharExpr2. INSTR returns type NUMBER.

If you are using TimesTen type mode, for information on the INSTR function, refer to documentation from previous releases of TimesTen.

SQL syntax

{INSTR | INSTRB | INSTR4} ( CharExpr2, CharExp1 [,m[,n]])

Parameters

INSTR has the parameters:

Parameter	Description
`CharExpr1`	The substring to be found in string `CharExpr2.` If CharExpr1 does not occur in `CharExpr2`, then zero is returned. If either string is of length zero, NULL is returned.
`CharExpr2`	The string to be searched to find the position of `CharExpr1.`
`m`	The optional position at which to begin the search. If `m` is specified as zero, the result is zero. If `m` is positive, the search begins at the `CharExpr2`+`m`. If `m` is negative, the search begins m characters from the end of `CharExpr2`.
`n`	If `n` is specified it must be a positive value and the search returns the position of the `n`th occurrence of `CharExpr1`

Description

INSTR calculates strings using characters as defined by character set. INSTRB uses bytes instead of characters. INSTR4 uses UCS4 code points.

Examples

The following example uses INSTR to determine the position at which the substring 'ing' occurs in the string 'Washington':

Command> SELECT INSTR ('Washington', 'ing') FROM dual;
< 5 >
1 row found.

LENGTH

Returns the length of a given character string in an expression. LENGTH returns type NUMBER.

If you are using TimesTen type mode, for information on the LENGTH function, refer to documentation from previous releases of TimesTen.

SQL syntax

{LENGTH|LENGTHB|LENGTH4} (CharExpr)

Parameters

LENGTH has the parameter:

Parameter	Description
`CharExpr`	The string for which to return the length.

Description

The LENGTH functions return the length of CharExpr. LENGTH calculates the length using characters as defined by the character set. LENGTHB uses bytes rather than characters. LENGTH4 uses UCS4 code points.

Examples

To determine the length of the string 'William':

Command> SELECT LENGTH('William') FROM dual;
< 7 >
1 row found.