Description of the decode () function in SQL, sqldecode

Description of the decode () function in SQL, sqldecode

The DECODE function is a unique function computing method provided by ORACLE's SQL software oracle pl/SQL. It is well known for its concise calculation method, controllable data model, and flexible format conversion.

When I read other users' SQL statements today, I still see the decode () function. I have never touched it before. I checked it online. It is quite a useful function, write it down and hope it will help you!

Decode () function introduction:

Main function: translate the query result into other values (that is, it is expressed in other forms. The following is an example );

Usage:

Select decode (columnname, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value) From talbenameWhere...

Columnname is the column defined in the table to be selected,

· Meaning:

Decode (condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value) is understood as follows:

If (condition = value 1) then return (translation value 1) elsif (condition = value 2) then return (translation value 2 )...... elsif (condition = value n) then return (translation value n) else return (default value) end if

Note: The default value can be the column name you want to select, or Other values you want to define, such as Other;

Example:

A table named output is defined, and two columns are defined as monthid (var type) and sale (number type). If sale is set to 1000, = 2000 translate to C, = 3000 translate to B, = 4000 translate to A, if Other values translate to Other;

The SQL statement is as follows:

Select monthid , decode (sale,1000,'D',2000,'C',3000,'B',4000,'A','Other') sale from output

Special cases:

Compare with only one value

Select monthid ,decode（sale， NULL，‘---'，sale） sale from output

Other functions, such as nvl or sign (), can be used in decode;

NVL (EXPR1, EXPR2)

If EXPR1 is NULL, EXPR2 is returned; otherwise, EXPR1.

SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;

If the decode function is used

select monthid,decode(nvl(sale,6000),6000,'NG','OK') from output 

The sign () function returns 0, 1,-1, and,

If a smaller value is

Select monthid, decode (sign (sale-6000),-1, sale, 6000) from output to get a smaller value.

Summary:

Purpose: Compare the input value with the parameter list in the function, and return 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]);

The value of control _ value. The DECODE function compares the value with a series of even orders to determine the return value. Value1 is a numerical value that forms an ordinal pair. If the input value matches the value, the corresponding result is returned. Corresponding to an empty return value, you can use the keyword NULL to indicate that result1 is the result value of an ordinal pair. If default_result does not match any value, the default value returned by the function.

Articles you may be interested in:

• Common ASP functions: HTMLDecode
• Javascript UrlDecode Function Code
• Query the decode effect of a mysql-like oracle Database
• PHP beware of the SQL injection vulnerability caused by urldecode
• Php urlencode () and urldecode () function character encoding principles
• Javascript generates json Function Code (you can use json_decode of php to decode it)
• C # decoding function UrlDecode corresponding to escape in js
• PHP json_decode function detailed analysis