String Functions

	Print Open PDF Version of Online Help

Reports > Using Functions in Analyses > String Functions

String Functions

String functions perform various character manipulations, and they operate on character strings.

ASCII

Converts a single character string to its corresponding ASCII code, between 0 and 255.

Syntax:

ASCII (character_expression)

where:

character_expression

Any expression that evaluates to an ASCII character.

If the character expression evaluates to more than one character, the ASCII code corresponding to the first character in the expression is returned.

BIT_LENGTH

Returns the length, in bits, of a specified string. Each Unicode character is 2 bytes in length, which is equal to 16 bits.

Syntax:

BIT_LENGTH (character_expression)

where:

character_expression

Any expression that evaluates to character string.

CHAR

Converts a numerical value between 0 and 255 to the character value corresponding to the ASCII code.

Syntax:

CHAR (n_expression)

where:

n_expression

Any expression that evaluates to a numerical value between 0 and 255.

CHAR_LENGTH

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 (character_expression)

where:

character_expression

Any expression that evaluates to a numerical value between 0 and 255.

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.

Form 1 Syntax:

CONCAT (character_expression1, character_expression2)

where:

character_expression

Expressions that evaluate to character strings.

Form 2 Syntax:

string_expression1 || string_expression2 || ... string_expressionxx

where:

string_expression

Expressions that evaluate to character strings, separated by the character string concatenation operator || (double vertical bars). The first string is concatenated with the second string to produce an intermediate string, which is then concatenated with the next string, and so on.

Example:

Account."Account Name" ||'-'|| Account."Account Location"

Results look like:

Action Rentals - Headquarters

INSERT

Inserts a specified character string into a specified location in another character string, replacing a specified number of characters in the target string.

Syntax:

INSERT (character_expression1, n, m, character_expression2)

where:

character_expression1	Any expression that evaluates to a character string. This is the string receiving the insertion.
character_expression2	Any expression that evaluates to a character string. This is the string to insert.
n	Any positive integer representing the number of characters from the start of the first string where a portion of the second string is inserted.
m	Any positive integer representing the number of characters in the first string to be replaced by the entirety of the second string.

LEFT

Returns a specified number of characters from the left of a string.

Syntax:

LEFT (character_expression, n)

where:

character_expression	Any expression that evaluates to a character string.
n	Any positive integer representing the number of characters from the left of the string that are returned.

LENGTH

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

Syntax:

LENGTH (character_expression)

where:

character_expression

Any expression that evaluates to a character string.

LOCATE

Returns the numerical position of one string within another string. If the string is not found, the LOCATE function returns a value of 0. If you want to specify a starting position to begin the search, use the LOCATEN function instead.

Syntax:

LOCATE (character_expression1, character_expression2)

where:

character_expression1	Any expression that evaluates to a character string. This is the expression to search for in the character expression.
character_expression2	Any expression that evaluates to a character string. This is the expression to be searched.

LOCATEN

Returns the numerical position of one string within another string. This is identical to the LOCATE function, except that the search begins at the position specified by an integer argument. If the string is not found, the LOCATEN function returns a value of 0. The numerical 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:

LOCATEN (character_expression1, character_expression2, n)

where:

character_expression1	Any expression that evaluates to a character string. This is the string to search for.
character_expression2	Any expression that evaluates to a character string. This is the string to be searched.
n	Any positive, nonzero integer that represents the starting position to begin to look for the locate expression.

LOWER

Converts a character string to lower case.

Syntax:

LOWER (character_expression)

where:

character_expression

Any expression that evaluates to a character string.

OCTET_LENGTH

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

Syntax:

OCTET_LENGTH (character_expression)

where:

character_expression

Any expression that evaluates to a character string.

POSITION

Returns the numerical position of one string within another string. If the string is not found, the function returns 0.

Syntax:

POSITION (character_expression1 IN character_expression2)

where:

character_expression1	Any expression that evaluates to a character string. This is the string to search for.
character_expression2	Any expression that evaluates to a character string. This is the string in which to search.

REPEAT

Repeats a specified expression n times, where n is a positive integer.

Syntax:

REPEAT (character_expression, n)

REPLACE

Replaces specified characters in a string with other specified characters.

Syntax:

REPLACE (character_expression, change_expression, replace_with_expression)

where:

character_expression	Any expression that evaluates to a character string. This string will have its characters replaced.
change_expression	Any expression that evaluates to a character string. Specifies the characters in the first string that will be replaced.
replace_with_expression	Any expression that evaluates to a character string. Specifies the replacement characters to substitute into the first string.

RIGHT

Returns a specified number of characters from the right of a string.

Syntax:

RIGHT (character_expression, n)

where:

character_expression	Any expression that evaluates to a character string.
n	Any positive integer representing the number of characters from the right of the first string that are returned.

SPACE

Inserts blank spaces.

Syntax:

SPACE (integer)

where:

integer

Any expression that evaluates to a character string.

SUBSTRING

Creates a new string starting from a fixed number of characters into the original string.

Syntax:

SUBSTRING (character_expression FROM starting_position)

where:

character_expression	Any expression that evaluates to a character string.
starting_position	A positive integer that indicates the starting position within the first character string.

Example:

SUBSTRING ('ABCDEF' FROM 3)

Results in:

CDEF

TRIM

Strips specified leading and/or trailing characters from a character string.

Syntax:

TRIM (type 'character' FROM character_expression)

where:


type	LEADING TRAILING BOTH	Strips specified leading characters from a character string. Strips specified trailing characters from a character string. Strips specified characters, both leading and trailing, from a character string.
character		Any single character. If the character part of the specification and the single quotes are omitted, a space character is used as a default.
character_expression		Any expression that evaluates to a character string.

NOTE: The syntax TRIM (character_expression) is also valid. This trims all leading and trailing spaces.

UPPER

Converts a character string to uppercase.

Syntax:

UPPER (character_expression)

where:

character_expression

Any expression that evaluates to a character string.


Published 8/22/2016	Copyright © 2005, 2016, Oracle. All rights reserved. Legal Notices.