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

Compute, Validate and Rebuild Indexes in Oracle

Step 1: Save the below script in some particular location (suppose the path is D:\Exec_Indexing.sql) with extension .Sql

spool d:\Indexing_Script.sql;

select 'analyze index SCOTT.' || object_name || ' compute statistics;' || chr(10) || 'analyze index  SCOTT.' || object_name || ' validate structure;' || chr(10) || 'alter index  SCOTT.' || object_name || ' rebuild online;' from all_objects where owner='SCOTT' and object_type='INDEX';

spool off;

@d:\Indexing_Script.sql

Step 2: Run the above created .sql script (Exec_Indexing.sql) from SQL*PLUS or Command prompt as shown below:

SQL> @D:\ Exec_Indexing.sql

Output: in one go the above statement will automatically compute, Validate and Rebuild all the Indexes present within SCOTT Schema.