To Compare “NULL” Values Use “NVL” Function in Oracle

Query to compare “Null” values in oracle tables:

Step1: Create a Table “Test1” as shown below:

CREATE TABLE TEST1 (A NUMBER,B NUMBER);

Step 2: Insert few random data into “TEST1” Table.

INSERT INTO TEST1 VALUES (1,25);

INSERT INTO TEST1 VALUES (2,20);

INSERT INTO TEST1 VALUES (3,NULL);

INSERT INTO TEST1 VALUES (4,30);

INSERT INTO TEST1 VALUES (5,NULL);

Step 3: Select the “TEST1” Table.

SELECT * FROM TEST1;

A	B
1	25
2	20
3
4	30
5

Step4: Create a Table “Test2” as shown below:

CREATE TABLE TEST2 (A NUMBER,B NUMBER);

Step 5: Insert few random data into “TEST2” Table.

INSERT INTO TEST2 VALUES (1,25);

INSERT INTO TEST2 VALUES (2,NULL);

INSERT INTO TEST2 VALUES (3,20);

INSERT INTO TEST2 VALUES (4,NULL);

INSERT INTO TEST2 VALUES (5,NULL);

Step 6: Select the “TEST2” Table.

SELECT * FROM TEST2;

A	B
1	25
2
3	20
4
5

The column “A” of table “TEST1” and “TEST2” is same and can be act as a joining condition for both tables.

Run the below query to fetch column B’s data from both the table that is not equal.

SELECT TEST1.A,TEST1.B,TEST2.B FROM TEST1,TEST2 WHERE TEST1.A=TEST2.A AND TEST1.B!=TEST2.B

Output: No Rows Returned. (It is because null values cannot be equal to or unequal to any value)

Now, run the below query to fetch the actual result using NVL function.

SELECT TEST1.A,TEST1.B,TEST2.B FROM TEST1,TEST2 WHERE TEST1.A=TEST2.A AND NVL(TEST1.B,0)!=NVL(TEST2.B,0)

Expected Output:

A	B	B_1
2	20
3		20
4	30

SQL Query to Fetch Numeric Data from String value in Oracle

Query to fetch Numeric Data from String value in an Oracle Table:

Step 1: Create a Table “Test” as shown below:

CREATE TABLE TEST (A VARCHAR2(30));

Step 2: Insert string data value into “TEST” Table.

INSERT INTO TEST VALUES ('1DA2SDJD+4567@@##HGSD89HH0');

Step 3: Select the “TEST” Table.

SELECT * FROM TEST;

A

1DA2SDJD+4567@@##HGSD89HH0

Step 4: Run the below query to fetch “Numeric” value from String data.

select regexp_replace(A,'[^0-9]') as a FROM TEST3;

Output:

A

124567890

SQL Query to Fetch Order-wise Month from Date Column in Oracle

Query to fetch order-wise “Month” from Date column in an Oracle Table:

Step 1: Create a Table “Test” as shown below:

CREATE TABLE TEST (A DATE);

Step 2: Insert few random Date values into “TEST” Table.

INSERT INTO TEST VALUES('01-JAN-12');

INSERT INTO TEST VALUES('11-FEB-12');

INSERT INTO TEST VALUES('21-MAR-12');

INSERT INTO TEST VALUES('30-APR-12');

INSERT INTO TEST VALUES('01-MAY-12');

INSERT INTO TEST VALUES('02-JUN-12');

INSERT INTO TEST VALUES('06-JUL-12');

INSERT INTO TEST VALUES('10-AUG-12');

INSERT INTO TEST VALUES('30-SEP-12');

INSERT INTO TEST VALUES('28-OCT-12');

INSERT INTO TEST VALUES('18-NOV-12');

INSERT INTO TEST VALUES('31-DEC-12');

Step 3: Select the “TEST” Table.

SELECT * FROM TEST;

A

30-Apr-12

10-Aug-12

31-Dec-12

11-Feb-12

1-Jan-12

6-Jul-12

2-Jun-12

21-Mar-12

1-May-12

18-Nov-12

28-Oct-12

30-Sep-12

Step 4: Run the below query to fetch “MONTH” from Date Column “Order-wise”.

SELECT TO_CHAR(A,'MON') FROM TEST ORDER BY TO_CHAR(A,'MM');

Output:

TO_CHAR(A,'MON')

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

SQL Query to Fetch Even/Odd values in Oracle

To fetch Even/Odd values from a numeric column in an Oracle Table:

Step 1: Create a Table “Test” as shown below:

CREATE TABLE TEST(A NUMBER);

Step 2: Insert few random numeric values into “TEST” Table.

INSERT INTO TEST VALUES(1);

INSERT INTO TEST VALUES(15);

INSERT INTO TEST VALUES(4);

INSERT INTO TEST VALUES(26);

INSERT INTO TEST VALUES(497);

INSERT INTO TEST VALUES(590);

INSERT INTO TEST VALUES(9826);

INSERT INTO TEST VALUES(1003);

INSERT INTO TEST VALUES(333);

Step 3: Select the “TEST” Table.

SELECT * FROM TEST;

A
1
15
4
26
497
590
9826
1003
333

Step 4: Run the below query to fetch “EVEN” values from above Table column.

SELECT * FROM TEST WHERE MOD(A,2)=0;

Output:

A

4

26

590

9826

Step 5: Run the below query to fetch “ODD” values from above Table column.

SELECT * FROM TEST WHERE MOD(A,2)!=0;

Output:

A

1

15

497

1003

333