Three scalar functions (see Table 3, following) enable the manipulation and retrieval of string segments: SUBSTR, LENGTH, and VALUE.
| Function and Syntax | Argument | Result |
|---|---|---|
| LENGTH(argument) | Any data type | Integer represents the length of V |
| SUBSTR(S,N,L) | S: Character or graphic string to be evaluated. N: Binary integer represents the starting position of substring in S. L: Binary integer represents the length of substring. |
Substring of S |
| VALUE(arg1,arg2) | Arguments must have compatible data type. | A non-null value representing arg1 if arg1 is non-null, or representing arg2 if arg1 is null. |
The length function returns the actual variable length of the data if the data type is VARCHAR; it returns the fixed length if the data type is CHAR.
The following statement lists applicant status for each applicant in the Q.INTERVIEW table who was interviewed by manager 270. For any applicant, if the DISP column was not filled in (and therefore, contains a null value), the result for that row is "unknown" rather than the null symbol (-).
SELECT VALUE(DISP, 'unknown') FROM Q.INTERVIEW WHERE MANAGER = 270
The first or only argument of each of these functions is an expression giving the value to be manipulated or retrieved. For LENGTH, the value of this expression can be any data type. For SUBSTR, the value must be a character string or a graphic string. For VALUE, two or more values must be specified, and their data types must be comparable.
For example, this query finds the first initial and last name of an applicant with the temporary ID number 400.
SELECT SUBSTR(FIRSTNAME,1,1)||LASTNAME FROM Q.INTERVIEW WHERE TEMPID = 400[ Previous Page | Next Page | Contents | Index ]