extract only number part of column [message #448729] |
Thu, 25 March 2010 01:14  |
aijaz786
Messages: 91 Registered: February 2010
|
Member |
|
|
In a table, I have a column in a table with the following values:
targetcol
30 WD
180 WD
70%
0
80%
180 CD
70%
0
7 WD
2 WD
0
-
-
-
-
-
-
-
-
-
where WD is working days and CD Calendar days.
I just need to extract number from above. Any help will be appreciated.
Thahks.
|
|
|
|
|
|
|
|
|
|
Re: extract only number part of column [message #448773 is a reply to message #448763] |
Thu, 25 March 2010 02:45   |
 |
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Just adding to Michel Post for Yasir
SQL> with data as (select column_value from table
2 (in_list('30 WD,180 WD 190,70%,0,80%,180 CD,70%,0,7 WD,2 WD,0')))
3 select regexp_substr(column_value,'[url=http://www.orafaq.com/wiki/:digit:]:digit:[/url]*') from data
4 /
REGEXP_SUBSTR(COLUMN_VALUE,'[url=http://www.orafaq.com/wiki/:DIGIT:]:DIGIT:[/url]*')
--------------------------------------------------------------------------------
30
180
70
0
80
180
70
0
7
2
0
11 rows selected.
SQL> with data as (select column_value from table
2 (in_list('30 WD,180 WD 190,70%,0,80%,180 CD,70%,0,7 WD,2 WD,0')))
3 select regexp_replace(column_value,'[A-Z._%]') from data;
REGEXP_REPLACE(COLUMN_VALUE,'[A-Z._%]')
--------------------------------------------------------------------------------
30
180 190
70
0
80
180
70
0
7
2
0
11 rows selected.
sriram
|
|
|
|
|
|