Home » SQL & PL/SQL » SQL & PL/SQL » extract only number part of column (Oracle 10g and winddows 2003)
extract only number part of column [message #448729] Thu, 25 March 2010 01:14 Go to next message
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 #448738 is a reply to message #448729] Thu, 25 March 2010 01:38 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
select regexp_replace(targetcol,'[A-Z._%]') from tab1;
Re: extract only number part of column [message #448739 is a reply to message #448729] Thu, 25 March 2010 01:38 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
select TRIM(translate('189WD','ABCDEFGHIJKLMNOPQRSTUVWXYZ',' ')) from dual;
Re: extract only number part of column [message #448743 is a reply to message #448729] Thu, 25 March 2010 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: extract only number part of column [message #448749 is a reply to message #448743] Thu, 25 March 2010 01:52 Go to previous messageGo to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
If only digits are required why not to use REGEXP_SUBSTR
it should work your case.
Re: extract only number part of column [message #448760 is a reply to message #448738] Thu, 25 March 2010 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_substr('180 CD','[[:digit:]]*') from dual;
REG
---
180

Regards
Michel
Re: extract only number part of column [message #448763 is a reply to message #448729] Thu, 25 March 2010 02:28 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Whats wrong with regexp_replace?
Re: extract only number part of column [message #448770 is a reply to message #448763] Thu, 25 March 2010 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who say there is something wrong?

Just a remark with it: if you are multiple numbers in the same string you concatenate them. With regexp_substr you get the first one.

Regards
Michel

[Updated on: Thu, 25 March 2010 02:38]

Report message to a moderator

Re: extract only number part of column [message #448773 is a reply to message #448763] Thu, 25 March 2010 02:45 Go to previous messageGo to next message
ramoradba
Messages: 2454
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 Smile
Re: extract only number part of column [message #448830 is a reply to message #448729] Thu, 25 March 2010 05:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
aijaz786 wrote on Thu, 25 March 2010 07:14

where WD is working days and CD Calendar days.

I just need to extract number from above. Any help will be appreciated.

This does not make sense, unless you also want the non-digit part from it.
What does 70 mean? Is it WD? CD? %?

[Updated on: Thu, 25 March 2010 05:56]

Report message to a moderator

Re: extract only number part of column [message #448832 is a reply to message #448773] Thu, 25 March 2010 06:01 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Why in 180 WD 190, 180 190 should not come but 180?
Re: extract only number part of column [message #448844 is a reply to message #448832] Thu, 25 March 2010 06:36 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one says it is what it should come.

Regards
Michel
Previous Topic: consecutive dates
Next Topic: carry value
Goto Forum:
  


Current Time: Mon Sep 26 02:34:02 CDT 2016

Total time taken to generate the page: 0.06072 seconds