Neglecting / Ignoring Characters from a Alphanumeric value

How to neglect / ignore the characters from Alphanumeric values in an attribute/column, usage of Oracle Regular Expressions

How to neglect the characters from number values in an attribute/column

This is common mistake while creating the database and defining the attributes. Most of the people create table with varchar2() datatype and after some time they usually required that field to be in a number format.
This is commonly required to have some calculations on this field.
The question might be 
How to convert varchar2() to number?
How to fetch only number values from varchar2() attribute/column?
…
Well I am going to discuss here a case along with example.
Consider that we have a table like this

ID	YARN_COUNT	REQUIRED_DATA
1	150D	150
2	30/	30
3	20	20

The above mentioned table clearly gives us the required output. 
How to achieve this task?

1 Way: 
Conventional way of coding, using the case function.

SELECT ID,YARN_COUNT,
CASE WHEN YARN_COUNT LIKE '%D%' THEN
SUBSTR(YARN_COUNT,1,INSTR(YARN_COUNT,'D')-1)
WHEN YARN_COUNT LIKE '%/%' THEN
SUBSTR(YARN_COUNT,1,INSTR(YARN_COUNT,'/')-1)
ELSE YARN_COUNT 
END REQUIRED_DATA
FROM CONSTRUCTION;

This would give u the required results. This can be run on any database version prior to oracle 9i. But here have a problem that if we have more than three characters then the code will exceed according to the requirement. So according to requirement this is feasible code.
But prior to 10g we have Regular Expressions to resolve this problem.
Let me explain this newly introduced expression in oracle.

2 Way: 

Using the regexp_replace expression:

SELECT ID, YARN_COUNT, to_number(regexp_replace(YARN_COUNT,'[^[:digit:][:space:]]')) REQUIRED_DATA
FROM CONSTRUCTION;

3 Way: 

Using the Translate expression:

SELECT ID, YARN_COUNT, translate(YARN_COUNT,'ABCDEFGHIJKLMNOPQRSTUVWXYZ/',' ')
REQUIRED_DATA
FROM CONSTRUCTION;

While if you required to neglect the complete record that contain the character then the statement would be like.

SELECT YARN_COUNT FROM CONSTRUCTION WHERE regexp_like(YARN_COUNT,'^[[:digit:]]+$');

Metacharacter	Description
^	Anchor the expression to the start of a line
$	Anchor the expression to the end of a line
[	Start of character list
]	End of character list

Character Class	Description
[:alpha:]	Alphabetic characters
[:lower:]	Lowercase alphabetic characters
[:upper:]	Uppercase alphabetic characters
[:digit:]	Numeric digits
[:alnum:]	Alphanumeric characters
[:space:]	Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:punct:]	Punctuation characters
[:cntrl:]	Control characters (nonprinting)
[:print:]	Printable characters

Here are some useful queries got from link below: 
The next example shows the name Ellen Hildi Smith transformed to Smith, Ellen Hildi, by referring to the individual sub expressions by number. 

SELECT REGEXP_REPLACE(
'Ellen Hildi Smith',
'(.*) (.*) (.*)', '\3, \1 \2')
FROM dual

REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi



The displayed result shows the substring that identifies the duplicated words is. 

SELECT REGEXP_SUBSTR(
'The final test is is the implementation',
'([[:alnum:]]+)([[:space:]]+)\1') AS substr
FROM dual

SUBSTR
------
is is

You can find the details on regular expression on the given below link.
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt2.html