Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

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

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

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

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

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

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

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

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

 CHAR_LENGTH(source-string) + 1 - start-position
 

i.e. the remainder of source-string, starting at start-position, is returned.

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

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

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

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
Mimer SQL Documentation TOC PREV NEXT INDEX