OraFAQ Forum: SQL & PL/SQL » Display Numeric Value from Text Field

Home » SQL & PL/SQL » SQL & PL/SQL » Display Numeric Value from Text Field (Oracle 10 G, Windows7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Display Numeric Value from Text Field [message #683187]

Fri, 11 December 2020 02:18

hissam78
Messages: 186
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,

Report message to a moderator

Re: Display Numeric Value from Text Field [message #683188 is a reply to message #683187]

Fri, 11 December 2020 02:37

Michel Cadot
Messages: 68421
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.

Report message to a moderator

Re: Display Numeric Value from Text Field [message #683189 is a reply to message #683187]

Fri, 11 December 2020 06:11

Solomon Yakobson
Messages: 3214
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.

Report message to a moderator

Re: Display Numeric Value from Text Field [message #683193 is a reply to message #683189]

Sat, 12 December 2020 14:37

hissam78
Messages: 186
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..

Report message to a moderator

Re: Display Numeric Value from Text Field [message #683194 is a reply to message #683193]

Sun, 13 December 2020 00:31

Michel Cadot
Messages: 68421
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 01 09:32:33 CDT 2023