Display Numeric Value from Text Field [message #683187] |
Fri, 11 December 2020 02:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/06b42bb1d9c7dd3931310cac212d7c22?s=64&d=mm&r=g) |
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Dear expert
we have the following TABLE WITH "Code", "Desc_d"
AND
"Output" COLUMNS we need TO get NUMERIC value FROM "Desc_d" COLUMN
AND
display IN "Output" COLUMN AS shown below
somebody can help Please?
code DESC_d output
1001 1 pawa 1
1002 1.25 pawa 1.25
1003 1.5 pawa 1.5
1004 2 pawa 2
1005 2.5 pawa 2.5
Test Case:
CREATE TABLE data_Production(
Code number,
Desc_d varchar2(70));
INSERT INTO data_Production
(Code
,Desc_d)
VALUES
(1001 ,'1 pawa')
INSERT INTO data_Production
(Code
,Desc_d)
VALUES
(1002,'1.25 pawa')
INSERT INTO data_Production
(Code
,Desc_d)
VALUES (1003,'1.5 pawa')
INSERT INTO data_Production
(Code
,Desc_d)
VALUES (1004,'2 pawa')
INSERT INTO data_Production
(Code
,Desc_d)
VALUES (1005,'2.5 pawa')
commit;
thankful,
regards,
|
|
|
|
Re: Display Numeric Value from Text Field [message #683189 is a reply to message #683187] |
Fri, 11 December 2020 06:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
It looks like DESC_D always starts with number followed by a space and then some text. If so, using SUBSTR/INSTR will be faster than regular expressions:
select code,
desc_d,
to_number(substr(desc_d,1,instr(desc_d,' ') - 1)) output
from data_production
/
CODE DESC_D OUTPUT
---------- ---------- ----------
1001 1 pawa 1
1002 1.25 pawa 1.25
1003 1.5 pawa 1.5
1004 2 pawa 2
1005 2.5 pawa 2.5
SQL>
And if DESC_D is always number followed by a space followed by pawa then plain replace would do:
select code,
desc_d,
to_number(replace(desc_d,' pawa')) output
from data_production
/
CODE DESC_D OUTPUT
---------- ---------- ----------
1001 1 pawa 1
1002 1.25 pawa 1.25
1003 1.5 pawa 1.5
1004 2 pawa 2
1005 2.5 pawa 2.5
SQL>
SY.
|
|
|
|
Re: Display Numeric Value from Text Field [message #683194 is a reply to message #683193] |
Sun, 13 December 2020 00:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
As you can see with Solomon's answer and mine, you have to think about and specify your case in details.
In this case: Is number anywhere in the string? is it always at the beginning ending with a space (or another separator? Is it always with a specific string which can be easily removed? Can there be several number?...
Solution depends on all these questions and many more.
This is why your test case data must represent all cases yu have or can have.
|
|
|