Design

Back Next

decode

Searches a column for a value that you specify. If the function finds the value, it returns a result value, which you define. You can build an unlimited number of searches within a decode function.

If you use decode to search for a value in a string column, you can either trim trailing blank characters with the rtrim function or include the blanks in the search string.

Syntax

util:decode(value, search1, result1, args, default)

The following table describes the arguments:

Argument	Required/ Optional	Description
value	Required	Passes the values that you want to search. You can enter any valid transformation expression. You can pass any data type except Binary. To pass a NULL value, you must specify an empty sequence in the following format: ()
search1	Required	Passes the values for which you want to search. You can enter any valid transformation expression. You can pass any value with the same data type as the value argument. The search value must match the value argument. You cannot search for a portion of a value. Also, the search value is case sensitive. For example, if you want to search for the string 'Halogen Flashlight' in a particular column, you must enter 'Halogen Flashlight, not just 'Halogen'. If you enter 'Halogen', the search does not find a matching value. To pass a NULL value, you must specify an empty sequence in the following format: ()
result1	Required	The value that you want to return if the search finds a matching value. You can enter any valid transformation expression and pass any data type except Binary. To pass a NULL value, you must specify an empty sequence in the following format: ()
args	Required	Pairs of search values and result values separated by a comma. For example, use the following syntax: util:decode(value, search1, result1, search2, result2, searchn, resultn, default) To pass a NULL value, you must specify an empty sequence in the following format: ()
default	Required	The value that you want to return if the search does not find a matching value. You can enter any valid transformation expression and pass any data type except Binary. To pass a NULL value, you must specify an empty sequence in the following format: ()

Return Value

Returns the

$resultX

corresponding to the first

$searchX

that equals

$arg

If no match is found, returns

$default

if provided.

If no match and no default, returns an empty sequence

()

Even if multiple conditions are met, decode returns the first matching result.

Examples

The following table lists some sample values and return values:

SAMPLE FUNCTION	OUTPUT
util:decode("apple", "apple", "Fruit", ("carrot", "Vegetable"), "Unknown")	"Fruit"
util:decode(3, 1, "One", (2, "Two", 3, "Three"), "N/A")	"Three"
util:decode("car", "bike", "Two-wheel", ("car", "Four-wheel"), "Unknown")	"Four-wheel"
util:decode("bus", "bike", "Two-wheel", ("car", "Four-wheel"), "Not found")	"Not found"
util:decode("bus", "bike", "Two-wheel", "car", "Four-wheel")	()