Shulou(Shulou.com)06/02 Report--

Editor to share with you how to use the decode function in Oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

DECODE meaning

Decode (condition, value 1, return value 1, value 2, return value 2, … Value n, return value n, default)

This is the expression of decode, which is interpreted as:

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) usage of END IFDECODE

The main point here is the use of decode, which is very useful in many cases.

1. Translation value

Data screenshot:

Demand: query data, 1 for boys, 2 for girls

Select t.id, t.name, t.age, decode (t.sex,'1', 'boy','2', 'girl', 'other') as sex from STUDENT2 t

Results:

2.decode comparison size

Note: the sign (value) function will return 0, 1, 1, 1, respectively, based on the value of value as 0, positive and negative, respectively.

Data:

Demand: those over 20 show more than 20, those under 20 show less than 20, and 20 shows exactly 20.

Select t.id, t.name, t.age, decode (sign (t.age-20), 1, 'above 20',-1, 'below 20', 0, 'exactly 20,' unknown') as sex from STUDENT2 t

Results:

3.decode segment

Data is not available for now

Demand: a salary greater than 5000 is a high salary, a salary between 3000 and 5000 is medium, and a salary less than 3000 is a basic salary.

Select name, sal, decode (sign (sal-5000), 1, 'high salary', 0, 'high salary',-1, decode (sign (sal-3000), 1, 'medium', 0, 'medium',-1, 'low salary') as salname from person

The result is not available yet.

4. Search string

Data:

Need: find a name with three

Select t.id, decode (instr (t.name, 'three'), 0, 'name does not contain three', 'name contains three') as name, t.age, t.sex from STUDENT2 t

Results:

5. Determine whether it is empty or not

Data:

Demand: gender empty display "no data temporarily", not empty as-is output

Select t.id, t.name, t.age, decode (t.sexMagol null'No data yet', t.sex) as sex from STUDENT2 t

Results:

The above is all the contents of the article "how to use the decode function in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.