| Print Open PDF Version of Online Help

#### 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 August 2022 | Copyright © 2005, 2022, Oracle and/or its affiliates. Legal Notices.