DECODE function: Compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value. Different from other SQL functions, DECODE
DECODE function: Compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value. Different from other SQL functions, DECODE
DECODE function: Compares the input value with the parameter list in the function and returns a corresponding value based on the input value. The parameter list of a function is a sequence of several numbers and their corresponding result values. Of course, if it fails to match any real argument sequence, the function also has the default return value.
Unlike other SQL functions, the DECODE function can recognize and operate null values.
Syntax:
DECODE (control_value, value1, result1 [, value2, result2…] [, Default_result]);
Control _ value
The value to be processed. The DECODE function compares the value with a series of even orders to determine the return value.
Value1
Is the value of an ordinal pair. If the input value matches the value, the corresponding result is returned. Corresponds to an empty return value. You can use the keyword NULL
Result1
Is the result value of an ordinal pair.
If default_result does not match any value, the default value returned by the function.
Example:
Select decode (x, 1, 'x is 1', 2, 'x is 2', 'others ') from dual
If x is equal to 1, 'x is 1' is returned '.
If x is equal to 2, 'x is 2' is returned '.
Otherwise, others is returned '.
When we need to compare two values, we can use them together with the SIGN () function.
Select decode (SIGN (5-6), 1 'Is Positive',-1, 'Is Nagative', 'Is Zero ')
Similarly, you can use CASE to implement:
Select case sign (5-6)
WHEN 1 THEN 'is Positive'
WHEN-1 THEN 'Is Nagative'
ELSE 'is 0' END
FROM DUAL
In addition, you can use Decode in Order.
For example, the table_subject table has the subject_name column. Sort by language, number, and external order. In this case, you can easily use Decode to complete the requirements.
Select * from table_subject order by decode (subject_name, '', 1, 'mat', 2, '', 3)
