Oracle® Business Intelligence Server Administration Guide > Oracle BI Server SQL Reference > SQL Reference >
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 (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: CONCAT (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, and then this string is concatenated with the next string, and so on. |
Insert
Inserts a specified character string into a specified location in another character string. Syntax: INSERT(character_expression, n, m, character_expression)
where:
character_expression |
Any expression that evaluates to a character string. |
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 first 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 the character_expression1 in a character expression. If the character_expression1 is not found in the character expression, 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 the character_expression1 in a character expression. This is identical to the Locate function, except that the search for the pattern begins at the position specified by an integer argument. If the character_expression1 is not found in the character expression, 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: LOCATE(character_expression1, character_expression2, n)
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. |
n |
Any positive, nonzero integer that represents the starting position to being 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 the character_expression1 in a character expression. If the character_expression1 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. Used to search in the second string. |
character_expression2 |
Any expression that evaluates to a character string. |
Repeat
Repeats a specified expression n times, where n is a positive integer. Syntax: REPEAT(character_expression, n)
where:
character_expression |
Any expression that evaluates to a character string. |
n |
Any positive integer. |
Replace
Replaces specified characters from a specified character expression 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 first string is the original string. |
change_expression |
Any expression that evaluates to a character string. This second string specifies characters from the first string that will be replaced. |
replace_with_expression |
Any expression that evaluates to a character string. This third string specifies the 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. |
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 |
Any positive integer representing the number of characters from the start of the string where the result begins. |
TrimBoth
Strips specified leading and trailing characters from a character string. Syntax: TRIM (BOTH 'character' FROM character_expression)
where:
character |
Any single character. If the character part of the specification (and the single quotes) are omitted, a blank character is used as a default. |
character_expression |
Any expression that evaluates to a character string. |
TrimLeading
Strips specified leading characters from a character string. Syntax: TRIM (LEADING 'character' FROM character_expression)
where:
character |
Any single character. If the character part of the specification (and the single quotes) are omitted, a blank character is used as a default. |
character_expression |
Any expression that evaluates to a character string. |
TrimTrailing
Strips specified trailing characters from a character string. Syntax: TRIM (TRAILING 'character' FROM character_expression)
where:
character |
Any single character. If the character part of the specification (and the single quotes) are omitted, a blank character is used as a default. |
character_expression |
Any expression that evaluates to a character string. |
Upper
Converts a character string to uppercase. Syntax: UPPER (character_expression)
where:
character_expression |
Any expression that evaluates to a character string. |
|