How to use the decode function in oracle

This article mainly introduces examples of how to use the decode function in Oracle. It is quite good. I hope everyone can master the use of the decode function after learning it. Let’s take a look together.

Several uses of decode

1: Use decode to determine whether the strings are the same

DECODE(value,if1, then1,if2,then2,if3,then3,...,else)

means
IF condition=value 1 THEN
　　RETURN(value 1)

ELSIF condition = value 2 THEN
RETURN(value 2)
……

ELSIF condition = value n THEN
RETURN(value 3)
ELSE
RETURN(default)
END IF

sql test

select empno,decode(empno,7369,&#39;smith&#39;,7499,&#39;allen&#39;,7521,&#39;ward&#39;,7566,&#39;jones&#39;,&#39;unknow&#39;) as name from emp where rownum<=10

Output result

7369 smith
7499 allen
7521 ward
7566 jones
7654 unknow
7698 unknow
7782 unknow
7788 unknow
7839 unknow
7844 unknow

2: Use decode to compare sizes

select decode(sign(var1-var2),-1, var 1,var2) from dual

sign() function returns 0, 1, or -1 respectively depending on whether a value is 0, a positive number, or a negative number

sql Test

select decode(sign(100-90),-1,100,90) from dual

Output result

90

100-90=10>0 then it will Returns 1, so the final value of the decode function is 90

Anyway

select decode(sign(100-90),1,100,90) from dual

Output result

100

##100-90=10>0 returns 1, the judgment result is 1, returns the first variable 100, and the final output result is 100

3: Use decode function segmentation

Salary greater than 5000 is high salary, salary between 3000 and 5000 is medium salary, salary less than 3000 is low salary

sql Test

SELECT 
  ename,sal,
  DECODE(SIGN(sal - 5000),
      1,
      'high sal',
      0,
      'high sal',
      - 1,
      DECODE(SIGN(sal - 3000),
          1,
          'mid sal',
          0,
          'mid sal',
          - 1,
          DECODE(SIGN(sal - 1000),
              1,
              'low sal',
              0,
              'low sal',
              - 1,
              'low sal')))
FROM
  emp

Output result

SMITH  800  low sal
ALLEN 1600 low sal
WARD 1250 low sal
JONES 2975 low sal
MARTIN 1250 low sal
BLAKE  2850 low sal
CLARK 2450 low sal
SCOTT 3000 mid sal
KING 5000 high sal
TURNER 1500 low sal
ADAMS 1100 low sal
JAMES 950     low sal
FORD 3000 mid sal
MILLER 1300 low sal

4: Use decode to implement the table Or try to convert rows and columns

sql test

SELECT 
    SUM(DECODE(ENAME,'SMITH',SAL,0)) SMITH,
    SUM(DECODE(ENAME,'ALLEN',SAL,0)) ALLEN,
    SUM(DECODE(ENAME,'WARD',SAL,0))  WARD,
    SUM(DECODE(ENAME,'JONES',SAL,0)) JONES,
    SUM(DECODE(ENAME,'MARTIN',SAL,0)) MARTIN FROM EMP

The output results are as follows

SMITH ALLEN WARD  JONES MARTIN
 800 1600  1250    2975    1250

5: Use the decode function to search for strings using expressions

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode function compares the expression and the search word. If it matches, it returns the result; if it does not match, it returns the default value; if the default value is not defined, then Returns a null value.

sql test

SELECT 
  ENAME,
  SAL,
  DECODE(INSTR(ENAME, 'S'),
      0,
      '不含有s',
      '含有s') AS INFO
FROM
  EMP

Output result

SMITH 800      含有s
ALLEN 1600  不含有s
WARD 1250  不含有s
JONES 2975   含有s
MARTIN 1250   不含有s
BLAKE 2850   不含有s
CLARK 2450   不含有s
SCOTT 3000  含有s
KING 5000  不含有s
TURNER 1500  不含有s
ADAMS 1100  含有s
JAMES 950       含有s
FORD 3000  不含有s
MILLER 1300  不含有s

Decode function is very useful in actual development

Combined with the Lpad function, how to automatically add 1 to the value of the primary key and add 0 in front

select LPAD(decode( count(record number),0,1,max(to_number(record number)+1)),14,'0') record number from tetdmis

eg:

select decode(dir,1,0,1) from a1_interval

The value of dir changes from 1 to 0, and from 0 to 1

For example, I Want to query the number of boys and girls in a certain class?

Usually we write like this:

select count(*) from table where Gender = Male;
select count(*) from table where gender=female;

If you want to display them together, you have to union them, which is too troublesome

Use decode, just one sentence

select sum(decode(gender, male, 1, 0)), sum(decode(gender, female, 1, 0)) from table

eg:

select sum(decode(siteno,'LT',1,0)),sum(decode(siteno,'SZ',1,0)) from facd605;

select sum(case siteno when 'LT' then 1 else 0 end),sum(case siteno when 'SZ' then 1 else 0 end) from facd605;

Have you all learned it? Hurry up and give it a try.

Related recommendations:

Issues to note with the json_decode function in php

Detailed explanation of Oracle’s commonly used function Trunc

Detailed explanation of Oracle custom split function example

The above is the detailed content of How to use the decode function in oracle. For more information, please follow other related articles on the PHP Chinese website!