String Functions

String functions perform various manipulations on character strings. Place character
strings within single quotes.

Functions

ASCII

This function converts a single character string to its corresponding ASCII code,
between 0 and 255. If the character expression evaluates to multiple characters,
the ASCII code corresponding to the first character in the expression is returned.

Syntax

ASCII(strExpr)

Where

strExpr is any expression that evaluates to a character string.

 

 

BIT_LENGTH

This function returns the length, in bits, of a specified string. Each Unicode character
is 2 bytes in length (equal to 16 bits).

Syntax

BIT_LENGTH(strExpr)

Where

strExpr is any expression that evaluates to character string.

 

 

CHAR

This function converts a numeric value between 0 and 255 to the character value
corresponding to the ASCII code.

Syntax

CHAR(numExpr)

Where

numExpr is any expression that evaluates to a numeric value between 0 and
255.

 

 

CHAR_LENGTH

This function returns the length, in number of characters, of a specified string.
Leading and trailing blanks are not counted in the length of the string.

Syntax

CHAR_LENGTH(strExpr)

Where

strExpr is any expression that evaluates to a character string.

 

 

CONCAT

There are two forms of this function. The first form concatenates two character
strings. The second form uses the character string concatenation character to
concatenate more than two character strings.
To concatenate two strings

Syntax

CONCAT(strExpr1, strExpr2)

Where

strExpr are expressions that evaluate to character strings, separated by commas.

To concatenate more than two strings

Syntax

CONCAT(strExpr1, strExpr2 || strExpr3)

Where

strExprs are expressions that evaluate to character strings, separated by commas
and the character string concatenation operator || (double vertical
bars). First, strExpr2 is concatenated with strExpr3 to produce an intermediate
string, then both strExpr1 and the intermediate string are concatenated by the
CONCAT function to produce the final string.

 

 

INSERT

This function inserts a specified character string into a specified location in another
character string.

Syntax

INSERT(strExpr1, integer1, integer2, strExpr2)

Where

strExpr1 is any expression that evaluates to a character string. Identifies the
target character string.
integer1 is any positive integer that represents the number of characters from
the beginning of the target string where the second string is to be inserted.
integer2 is any positive integer that represents the number of characters in the
target string to be replaced by the second string.
strExpr2 is any expression that evaluates to a character string. Identifies the
character string to be inserted into the target string.

 

 

LEFT

This function returns a specified number of characters from the left of a string.

Syntax

LEFT(strExpr, integer)

Where

strExpr is any expression that evaluates to a character string.
integer is any positive integer that represents the number of characters from
the left of the string to return.

 

 

LENGTH

This function returns the length, in number of characters, of a specified string. The
length is returned excluding any trailing blank characters.

Syntax

LENGTH(strExpr)

Where

strExpr is any expression that evaluates to a character string.

 

 

LOCATE

This function returns the numeric position of a character string in another character
string. If the character string is not found in the string being searched, the function
returns a value of 0.
If you want to specify a starting position to begin the search, include the integer
argument. The numeric position to return is determined by counting the first character
in the string as occupying position 1, regardless of the value of the integer
argument.

Syntax

LOCATE(strExpr1, strExpr2 [, integer])

Where

strExpr1 is any expression that evaluates to a character string. Identifies the
string for which to search.
strExpr2 is any expression that evaluates to a character string. Identifies the
string to be searched.
integer is any positive (nonzero) integer that represents the starting position to
begin to look for the character string. The integer argument is optional.

Examples

This example returns 4 as the numerical position of the letter d in the character
string abcdef:
Locate(‘d’, ‘abcdef’)
This example returns 0, because the letter g is not found within the string being
searched.
Locate(‘g’, ‘abcdef’)
This example returns 4 as the numeric position of the letter d in the character string
abcdef. The search begins with the letter c, the third character in the string. The
numeric position to return is determined by counting the letter ‘a’ as occupying
position 1.
LOCATE(‘d’ ‘abcdef’, 3)
This example returns 0, because the letter b occurs in the string before the starting
position to begin the search.
LOCATE(‘b’ ‘abcdef’, 3)

 

 

LOWER

This function converts a character string to lowercase.

Syntax

LOWER(strExpr)

Where

strExpr is any expression that evaluates to a character string.

 

 

OCTET_LENGTH

This function returns the number of bits, in base 8 units (number of bytes), of a
specified string.

Syntax

OCTET_LENGTH(strExpr)

Where

strExpr is any expression that evaluates to a character string.

 

 

POSITION

This function returns the numeric position of strExpr1 in a character expression. If
strExpr1 is not found, the function returns 0. See also “LOCATE” for related information.

Syntax

POSITION(strExpr1 IN strExpr2)

Where

strExpr1 is any expression that evaluates to a character string. Identifies the
string to search for in the target string.
strExpr2 is any expression that evaluates to a character string. Identifies the
target string to be searched.

Examples

This example returns 4 as the position of the letter d in the character string abcdef:
POSITION(‘d’, ‘abcdef’)
This example returns 0 as the position of the number 9 in the character string
123456, because the number 9 is not found.
POSITION(‘9’, ‘123456’)

 

 

REPEAT

This function repeats a specified expression n times.

Syntax

REPEAT(strExpr, integer)

Where

strExpr is any expression that evaluates to a character string.
integer is any positive integer that represents the number of times to repeat
the character string.

Example

This example repeats abc four times:
REPEAT(‘abc’, 4)

 

 

REPLACE

This function replaces one or more characters from a specified character expression
with one or more other characters.

Syntax

REPLACE(strExpr1, strExpr2, strExpr3)

Where

strExpr1 is any expression that evaluates to a character string. This is the string
in which characters are to be replaced.
strExpr2 is any expression that evaluates to a character string. This second
string identifies the characters from the first string that are to be replaced.
strExpr3 is any expression that evaluates to a character string. This third string
specifies the characters to substitute into the first string.

Example

In the character string abcd1234, the characters 123 are replaced by the character
string zz:
REPLACE(‘abcd1234’, ‘123’, ‘zz’)

Result

abcdzz4

 

 

RIGHT

This function returns a specified number of characters from the right of a string.

Syntax

RIGHT(strExpr, integer)

Where

strExpr is any expression that evaluates to a character string.
integer is any positive integer that represents the number of characters from
the right of the string to return.

Example

This example returns the three rightmost characters from the character string
123456:
RIGHT(‘123456’, 3)

Result

456

 

 

SPACE

Inserts blank spaces.

Syntax

SPACE(integer)

Where

integer is any positive integer that indicates the number of spaces to insert.

 

 

SUBSTRING

This function creates a new string starting from a fixed number of characters into
the original string.

Syntax

SUBSTRING(strExpr FROM starting_position)

Where

strExpr is any expression that evaluates to a character string.
starting_position is any positive integer that represents the number of characters
from the start of the left side of the string where the result is to begin.

 

 

TRIMBOTH

This function strips specified leading and trailing characters from a character
string.

Syntax

TRIM(BOTH character FROM strExpr)

Where

character is any single character. If you omit this specification (and the
required single quotes), a blank character is used as the default.
strExpr is any expression that evaluates to a character string.

 

 

TRIMLEADING

This function strips specified leading characters from a character string.

Syntax

TRIM(LEADING character FROM strExpr)

Where

character is any single character. If you omit this specification (and the
required single quotes), a blank character is used as the default.
strExpr is any expression that evaluates to a character string.

 

 

TRIMTRAILING

This function strips specified trailing characters from a character string.

Syntax

TRIM(TRAILING character FROM strExpr)

Where

character is any single character. If you omit this specification (and the
required single quotes), a blank character is used as the default.
strExpr is any expression that evaluates to a character string.

 

 

UPPER

This function converts a character string to uppercase.

Syntax

UPPER(strExpr)

Where

strExpr is any expression that evaluates to a character string.