String 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.

Syntax for Form 1 (To Concatenate Two Strings) 

CONCAT(strExpr1, strExpr2)

Where:

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

Example 

This example request returns the results shown.

SELECT DISTINCT CONCAT('abc', 'def') FROM employee
CONCAT('abc', 'def')

Result:

abcdef

Syntax for Form 2 (To Concatenate More Than Two Strings) 

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.

Example 

This example request returns the results shown.

SELECT DISTINCT CONCAT('abc','def' || 'ghi') FROM employee

Result:

abcdefghi

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.

Example 

In the first string, starting at the second position (occupied by the number 2), three characters (the numbers 2, 3, and 4) are replaced by the string abcd.

SELECT INSERT('123456', 2, 3, 'abcd') FROM table

Result:

1abcd56
1abcd56
...

LEFT

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.

Example 

This example returns the three leftmost characters from the character string 123456:

SELECT LEFT('123456', 3) FROM table

Result:

123
123
...

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 numeric 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:

SELECT right('123456', 3) FROM table

Result:

456

SPACE

This function 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.