String Functions
String functions perform various character manipulations, and they operate on character strings.
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.
Example:
ASCII('A')
This will give you the ASCII Code for the letter “A”, which is 65.
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.
Example:
CHAR(65)
This will give you the letter corresponding to the ASCII Code 65, which is the letter “A”.
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.
Example:
SUBSTRING("Candidate Identification"."Name" FROM 4 FOR CHAR_LENGTH("Candidate Identification"."Name"))
This will return the string within the Candidate Name starting with the 4th character and extending to the end of the record.
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.
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: OR
CONCAT("Candidate Identification"."Last Name", ‘, ‘||"Candidate Identification"."First Name,)This will return the string: Last Name, First Name.
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:
LEFT("Requisition Department"."Department Name",6)
This will return the first 6 characters of the Requisition Department Name.
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.
Example:
SUBSTRING("Candidate Identification"."Name" FROM 4 FOR LENGTH("Candidate Identification"."Name"))
This will return the string within the Candidate Name from the 4th character to the end of the record.
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.
Example:
substring("Requisition Department"."Department Level 1 Name",locate('-',"Requisition Department"."Department Level 1 Name")+1,length("Requisition Department"."Department Level 1 Name"))
This will return the string within the Req. Department Name starting from the first character after the dash (notice the +1), wherever it is located in the Department Name, to the end of the record.
This function converts a character string to lowercase.
Syntax
LOWER(strExpr)Where:
strExpr is any expression
that evaluates to a character string.
Example:
LOWER("Candidate Identification"."Name")
This will convert all upper case characters to lower case within the Candidate Name.
POSITION
This function returns the numeric position of strExpr1 in a character expression. If strExpr1 is not found, the function returns 0.
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')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:
REPLACE("Candidate Identification"."Name",',',' ')
This will replace all commas within the Candidate Name with spaces.
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
RIGHT("Requisition Department"."Department Name",6)
This will return the last 6 characters of the Requisition Department Name.
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.
Example:
SUBSTRING("Requisition Department"."Department Name",5,6)
Will give you 6 characters of the Requisition Department Name, starting at position 5.
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.
Example:
TRIM(BOTH '0' from "Requisition Identification"."Req. Identifier")
This will remove all leading and trailing zeros from the Requisition Number. Zeros in the middle will remain.
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.
Example:
TRIM(LEADING '0' from "Requisition Identification"."Req. Identifier")
This will remove all leading zeros from the Requisition Number.
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.
Example:
TRIM(TRAILING '0' from "Requisition Identification"."Req. Identifier")
This will remove all trailing zeros from the Requisition Number.
UPPER
This function converts a character string to uppercase.
Syntax
UPPER(strExpr)Where:
strExpr is any expression
that evaluates to a character string.
Example:
UPPER("Candidate Identification"."Name")
This will convert all lower case characters to upper case within the Candidate Name.