Home » SQL & PL/SQL » SQL & PL/SQL » Display Numeric Value from Text Field (Oracle 10 G, Windows7)
Display Numeric Value from Text Field [message #683187] Fri, 11 December 2020 02:18 Go to next message
hissam78
Messages: 142
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 #683188 is a reply to message #683187] Fri, 11 December 2020 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col desc_d format a15
SQL> select d.*, to_number(regexp_substr(desc_d,'[[:digit:]\.]*')) output from data_Production d;
      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

5 rows selected.
Note: your INSERT statements should also be ended by a ";" as you did it for CREATE TABLE and commit.
Re: Display Numeric Value from Text Field [message #683189 is a reply to message #683187] Fri, 11 December 2020 06:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
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 #683193 is a reply to message #683189] Sat, 12 December 2020 14:37 Go to previous messageGo to next message
hissam78
Messages: 142
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear Experts,
Grateful to both of you Geniuses, Both the Queries works fine, Bundle of thanks for prompt kind reply...

Sure Michel Cadot, I will follow your given instructions..
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
Michel Cadot
Messages: 67850
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.

Previous Topic: XML into one row
Next Topic: Ora-06502 error for json_value (merged)
Goto Forum:
  


Current Time: Thu May 13 05:17:06 CDT 2021