Data cleaning: Decode using the sign function to make small value judgments

Background
When processing a business data, it is necessary to determine the size of the two data fields and keep the large data below the specified fields. Consider using the Decode function and the sign function for this purpose.

Solution

1, decode (condition, value1, translation value1Value2, translation value2,... Value n, translation value n, default value)2, sign () function: According to a value of0, positive, negative, respectively, return0、1、-13, to get a larger or smaller value of two fields:A>b,A-b>0, sign (A-B) =1, get a large value that isA, Decode (sign (A-B),1,ABA<b,A-b<0, sign (A-B) =-1, getting a small value isA, Decode (sign (A-B),-1,AB

Experimental
Use the continuation table first to experiment with the effect, as follows:

select decode(sign(66-88),-1,66,88) from dual;

Returns a small value of two values as the display result, as follows:

Returns a large value of two values as the display result, as follows:

select decode(sign(66-88),1,66,88) from dual;

Start the experiment.
1. Create an Experiment table

CREATE TABLE PANDUAN (A VARCHAR2(50),B VARCHAR2(50));

2. Inserting experimental data

 INSERT  into Panduan (A, b) VALUES('+ ','* '); INSERT  into Panduan (A, b) VALUES('+', ' + '); INSERT  into Panduan (A, b) VALUES(' 278 ',' 698 '); INSERT  into Panduan (A, b) VALUES(' 4530 ',' 228 ');  COMMIT;

Querying experimental data

SELECT * FROM PANDUAN;

3, judge the size of the two fields, keep large data and small data, as follows:
Keep a larger number

SELECT DECODE(SIGN(A-B),1,A,B) FROM PANDUAN;

Keep a smaller number

SELECT DECODE(SIGN(A-B),-1,A,B) FROM PANDUAN;

Small knowledge, simple and remember.

Blue Growth Kee Series:

Original works, from "Deep Blue Blog" Blogs

The growth of Blue-chase DBA (1): Rushing to the road and into Shandong

Blue Growth Kee-Chase DBA (2): Install! Installation! A long-lost memory that caused me to re-perceive the DBA

The growth of Blue-Chase DBA (3): antique operation, data import and export become a problem

The growth of blue-chasing the DBA (4): Recalling the youth's sorrow, and then exploring Oracle installation

The growth of Blue-Chase DBA (5): No talking about business, annoying application system

The growth of Blue-Chase DBA (6): Work and life: small skills, great people

The growth of Blue-Chase DBA (7): Basic Command, foundation stone

The growth of Blue-chase DBA (8): Regain SP report, recall Oracle's Statspack experiment

The growth of Blue-Chase DBA (9): National Day, chasing DBA, new plan, new departure

The growth of the Blue-Chase DBA (10): flying knives to defend themselves, not expertise: fiddling with middleware WebSphere

The growth of Blue-chase DBA (11): The ease of coming home, the dizzy wake up

The growth of Blue-chase DBA (12): Seven days seven harvested SQL

The growth of blue-chasing DBAs (13): Coordinating hardware vendors, six stories: what you see and feel "servers, storage, switches ..."

The growth of Blue-chase DBA (14): An unforgettable "cloud" side, starting Hadoop deployment

The growth of Blue-Chase DBA (15): Think FTP is very "simple", who chengxiang twists

The growth of Blue-Chase DBA: The DBA also drank and was 捭阖

The growth of blue-chasing DBAs (17): sharing, or spending, learning to grow in the post-IoE era

The growth of Blue-chase DBA (18): A cluster failure on a small machine, caused by an IP replacement

The growth of Blue-Chase DBA (19): The episode on the road: Touching "frame" and "software system"

The growth of Blue-chase DBA (20): Why, build a library escort

Other chapters:

Soccer and Oracle Series (1): 32 Way Princes soldiers of, Oracle32 Process Alliance Group A bigger picture of the Brazilian Smon process

Soccer and Oracle Series (2): Brazil opener Preview, Oracle architecture Talk

Soccer and Oracle Series (3): Oracle process ranking, World Cup round is about to be fought!

Soccer with Oracle Series (4): From Brazil fiasco in Germany, think of the difference in RAC topology comparison!

Soccer and Oracle Series (5): Fifa14 game The missing DirectX Library is analogous to the RPM package from Oracle!

Soccer and Oracle Series (6): The Asian Cup with the library--Refueling Chinese team

Data cleaning: Decode using the sign function to make small value judgments