Scalar String Functions

http://developer.mimer.com

Scalar String Functions

The following sections describe scalar string functions.

ASCII_CHAR

Returns the character that has the given ASCII code value. The given ASCII code value should be in the range 0-255.
Syntax

Syntax for the ASCII_CHAR function:

code is a numeric expression representing an ASCII value.

Rules

If the value of code is between 0 and 255, the function returns a single character value, i.e. CHAR(1), otherwise the function returns NULL.
If the value of code is NULL, then the result of the function is NULL.

Example
 SET CHR_VAL = ASCII_CHAR(65);  -- sets CHR_VAL to 'A'
CURRENT_PROGRAM

Returns the name of an entered program.
Syntax

Syntax for the CURRENT_PROGRAM function:

Rules

The function returns the value of the most recently entered program as character varying value with an maximum length of 128.
If no program has been entered the result of the function is NULL.

Example

The following example returns the PROGRAM ident if entered, otherwise the session ident:
 SET CHR_STR = COALESCE(CURRENT_PROGRAM(), SESSION_USER); 
LOWER

Converts all uppercase letters in a character string to lowercase.
Syntax

Syntax for the LOWER function:

source-string is a character string expression.

Rules

The data type of the result is the same as the data type of source-string.
source-string is assumed to be in ISO 8859-1 format. All conversions to lowercase defined in the ISO 8859-1 standard are handled by LOWER, including national characters.
If the value of source-string is NULL, then the result of the function is NULL.
When the source-string is in Unicode format, all conversions to lowercase as defined in the ISO 8859-1 standard are handled by LOWER, including national characters.
For Unicode data (i.e. national character data types), the conversion is performed using case folding, e.g. converting the German word 'Straße' to upper case results in 'STRASSE'.

Note: The length of the result in this case is longer than the input value. This means that using UPPER/LOWER on a column may cause data truncation. See http://www.unicode.org/unicode/reports/tr21/ Case Mappings, for more information on Unicode case conversion.

Example
 SELECT CHAR_LENGTH(TRIM(DESCRIPTION)), LOWER(TRIM(DESCRIPTION))
 FROM   CHARGES;
PASTE

Returns a character string where a specified number of characters, beginning at a given position, have been deleted from a character string and replaced with a given string expression.
Syntax

Syntax for the PASTE function:

string-1 and string-2 are character or binary string expressions.

string-1 and string-2 must be of the same type, i.e. either both character or both binary.

start and length are integer value expressions.

Rules

The length characters in string-1, starting from position start are deleted from string-1. Then string-2 is inserted into string-1, at the 'point of deletion'. The resulting character or binary string is returned.
If the value of length is positive, the length characters to the right of start are deleted. If the value of length is negative, the length characters to the left of start are deleted.
The point-of-deletion is where the cursor would be if you had just used a text editor to select the characters, as described, and performed an edit-cut operation.

A value for start of less than 1 (zero or negative) specifies a position to the left of the beginning of string-1.
It is possible that the specified deletion may not actually affect any of the characters of string-1, in which case the paste operation produces the effect of a prepend.

If the value of any operand is NULL, then the result of the function is NULL.
string-2 must not contain Unicode characters outside the Latin 1 repertoire if string-1 is of character type.

Example
 SET CHR_STR = PASTE('TEST STRING', 6, 3, 'P');  -- sets CHR_STR to 'TEST PING'
REPEAT

Returns a character string composed of a specified string expression repeated a given number of times.
Syntax

Syntax for the REPEAT function:

sub-string is a character or binary string expression.

repeat-count is an integer expression.

Rules

The result is a character or binary string consisting of sub-string repeated repeat-count times.
If the value of repeat-count is zero, then the result of the function is a character or binary string of length zero.
If the value of repeat-count is less than zero, then the result of the function is NULL.
If the value of either operand is NULL, then the result of the function is NULL.

Example
 SET CHR_STR = REPEAT('ABC', 3);  -- sets CHR_STR to 'ABCABCABC'
REPLACE

Replaces all occurrences of a given string expression with another string expression in a character string.
Syntax

Syntax for the REPLACE function:

source-string, string-1 and string-2 are character or binary string expressions.

source-string, string-1 and string-2 must be of equal type, i.e. either all are character or all are binary.

Rules

All occurrences of string-1 found in source-string are replaced with string-2, the resulting character or binary string is returned.
If the value of any of the operands is NULL, then the result of the function is NULL.
string-2 must not contain Unicode characters outside the Latin 1 repertoire if source-string is of character type.

Example
 SET CHR_STR = REPLACE('TEST STRING', 'ST', 'NOR');  -- sets CHR_STR to 
                                                     -- 'TENOR NORRING'
SOUNDEX

Returns a character string value containing six digits that represent an encoding of the sound of the given string expression.
Syntax

Syntax for the SOUNDEX function:

source-string is a character string expression.

Rules

The function returns a character string value containing six digits that represent an encoding of the sound of source-string.
If source-string contains two or more words, they are effectively concatenated into a single word by ignoring the separating space characters.
If the SOUNDEX values for two strings compare to be equal then they sound the same.

SUBSTRING

Extracts a substring from a given string, according to specified start position and length of the substring.
Syntax

Syntax for the SUBSTRING function:

source-string is a character or binary string expression.

start-position and string-length are integer value expressions.

Rules

SUBSTRING returns a character or binary string containing string-length characters of source-string, starting at the character specified by start-position, and in the same sequence as they appear in source-string.
If any of these positions are before the start or after the end of source-string, then no character is returned for that position. If all positions are outside the source string, an empty string is returned.

The first character in source-string has position 1.
If the data type of source-string is variable-length character, then the result of the SUBSTRING function is a variable-length character with maximum string length equal to the maximum length of source-string.
If the data type of source-string is fixed-length character, then the result of the SUBSTRING function is a variable-length character with maximum string length equal to the fixed length of source-string.
If the data type of source-string is variable-length binary, then the result of the SUBSTRING function is a variable-length binary with maximum string length equal to the maximum length of source-string.
If the data type of source-string is fixed-length binary, then the result of the SUBSTRING function is a variable-length binary with maximum string length equal to the fixed length of source-string.
If string-length is negative, or if start-position is greater than the number of characters in source-string, the function fails and an error is returned.
If string-length is omitted then it is assumed to be:
 CHAR_LENGTH(source-string) + 1 - start-position
 

   i.e. the remainder of source-string, starting at start-position, is returned.
If the value of any operand is NULL, then the result of the function is NULL.
Character strings returned from a SUBSTRING function, inherit the collation from the source string.

TAIL

Returns the specified number of rightmost characters in a given character string.
Syntax

Syntax for the TAIL function:

source-string is a character or binary string expression.

count is an integer value expression.

Rules

The right-most count characters of source-string are returned.
If count is zero, an empty string is returned.
If count is less than zero, then the result of the function is NULL.
If the value of either operand is NULL, then the result of the function is NULL.

Example
 SET CHR_STR = TAIL('TEST STRING', 3);  -- sets CHR_STR to 'ING'
TRIM

Removes leading and/or trailing instances of a specified character from a string.
Syntax

Syntax for the TRIM function:

trim-character is a character or binary string expression of length 1.

source-string is a character or binary string expression.

source-string and trim-character must be of equal type, i.e. either must both be character or both binary.

Note: LEADING, TRAILING or BOTH is referred to as the trim-specification below.

Rules

If the data type of source-string is variable-length character, then the result of the TRIM function is a variable-length character with maximum string length equal to the maximum length of source-string.
If the data type of source-string is fixed-length character, then the result of the TRIM function is a variable-length character with maximum string length equal to the length of source-string.
If the data type of source-string is variable-length binary, then the result of the TRIM function is a variable-length binary with maximum string length equal to the maximum length of source-string.
If the data type of source-string is fixed-length binary, then the result of the TRIM function is a variable-length binary with maximum string length equal to the length of source-string.
If trim-specification is not specified, BOTH is implicit.
If trim-character is not specified, ' ' (space) is implicit.
If the length of trim-character is not 1, an error is returned.
If the value of either operand is NULL, then the result of the function is NULL.
Character strings returned from a TRIM function, inherit the collation from the source string.

UPPER

Converts all lowercase letters in a character string to uppercase.
Syntax

Syntax for the UPPER function:

source-string is a character string expression.

Rules

The data type of the result is the same as the data type of source-string.
source-string is assumed to be in ISO 8859-1 format. All conversions to uppercase defined in the ISO 8859-1 standard is handled by UPPER, including national characters.
If the value of source-string is NULL, then the result of the function is NULL.
When the source-string is in Unicode format, all conversions to uppercase as defined in the ISO 8859-1 standard are handled by UPPER, including national characters.
For Unicode data (i.e. national character data types), the conversion is performed using case folding.

Note: The length of a result may be longer than the input value. This means that using UPPER/LOWER on a column may cause data truncation. See
http://www.unicode.org/unicode/reports/tr21/ Case Mappings, for more information on Unicode case conversion.

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se