Bookshelf v7.5: String Functions

Siebel Analytics Server Administration Guide > 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, 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:

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, which is then 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.

character_expression	Any expression that evaluates to an ASCII character.

character_expression	Any expression that evaluates to character string.

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

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

character_expression	Expressions that evaluate to character strings.

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.

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.

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.

character_expression	Any expression that evaluates to a character string.

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.

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.

character_expression	Any expression that evaluates to a character string.

character_expression	Any expression that evaluates to a character string.

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.

character_expression	Any expression that evaluates to a character string.
n	Any positive integer.

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.

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.

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.

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.

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.

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.

character_expression	Any expression that evaluates to a character string.

Siebel Analytics Server Administration Guide
Published: 23 June 2003