OraFAQ Forum: SQL & PL/SQL » keep only numeric values in a string

Home » SQL & PL/SQL » SQL & PL/SQL » keep only numeric values in a string (oracle 11g, windows)

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

keep only numeric values in a string [message #654879]

Tue, 16 August 2016 06:44

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Hi All,

I have a column QUANTITY in table DETAILS where it has values like 123.aa, 233.00, 0456, 983* and so on.

I want the values to be like 123,233,456,983 eliminating all the special characters other than the numeric values.

How to write the select statement for it ??

Hope this is clear enough.

Thanks
Chinmaya

[Updated on: Tue, 16 August 2016 06:49]

Report message to a moderator

Re: keep only numeric values in a string [message #654880 is a reply to message #654879]

Tue, 16 August 2016 07:26

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Very easy to do. If you want only 0-9 to be kept

>select regexp_replace('123,233,456,983','[^0-9]') from dual;

REGEXP_REPLA
------------
123233456983

Report message to a moderator

Re: keep only numeric values in a string [message #654882 is a reply to message #654880]

Tue, 16 August 2016 07:29

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That doesn't look correct (at least, not if you try it with provided sample input which is '123.aa, 233.00, 0456, 983*').

Report message to a moderator

Re: keep only numeric values in a string [message #654883 is a reply to message #654882]

Tue, 16 August 2016 07:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

He asked for ONLY the numbers to be returned. He said nothing about sign characters or periods.

Report message to a moderator

Re: keep only numeric values in a string [message #654884 is a reply to message #654883]

Tue, 16 August 2016 07:32

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

He said that this output

123,233,456,983

is expected for this input

123.aa, 233.00, 0456, 983*

Report message to a moderator

Re: keep only numeric values in a string [message #654885 is a reply to message #654884]

Tue, 16 August 2016 07:34

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Agree with you Littlefoot

Report message to a moderator

Re: keep only numeric values in a string [message #654886 is a reply to message #654883]

Tue, 16 August 2016 07:34

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Your right, I misread the requirements

Report message to a moderator

Re: keep only numeric values in a string [message #654887 is a reply to message #654886]

Tue, 16 August 2016 07:36

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Again it is easy, but I need some additional information. What are you using to determine the order of the returned values and what version of the database are your running?

Report message to a moderator

Re: keep only numeric values in a string [message #654889 is a reply to message #654887]

Tue, 16 August 2016 07:40

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

it will keep only the numeric values keeping in mind it is in the same order.I am using oracle 11 g

Report message to a moderator

Re: keep only numeric values in a string [message #654890 is a reply to message #654889]

Tue, 16 August 2016 07:46

Bill B
Messages: 1971
Registered: December 2004

Senior Member

What are you using in the order by on your query. the only way to guarantee the order is to use an order by. But that being said use the following query, include your own order by

select LISTAGG(regexp_replace(QUANTITY,'[^0-9]'),',')
WITHIN GROUP (ORDER BY regexp_replace(QUANTITY,'[^0-9]')) STR
FROM DETAILS;

Report message to a moderator

Re: keep only numeric values in a string [message #654891 is a reply to message #654890]

Tue, 16 August 2016 07:55

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm afraid that it still doesn't work as expected:

SQL> WITH details AS (SELECT '123.aa, 233.00, 0456, 983*' quantity FROM DUAL)
  2  SELECT LISTAGG (REGEXP_REPLACE (QUANTITY, '[^0-9]'), ',')
  3            WITHIN GROUP (ORDER BY REGEXP_REPLACE (QUANTITY, '[^0-9]'))
  4            STR
  5    FROM DETAILS;

STR
--------------------------------------------------------------------------------
123233000456983

SQL>

This one is ugly, but it (kind of) works:

SQL> WITH test AS (SELECT '123.aa, 233.00, 0456, 983*' col FROM DUAL),
  2       test_1
  3       AS (    SELECT TRIM (REGEXP_SUBSTR (col,
  4                                           '[^,]+',
  5                                           1,
  6                                           LEVEL))
  7                         res
  8                 FROM test
  9           CONNECT BY REGEXP_SUBSTR (col,
 10                                     '[^,]+',
 11                                     1,
 12                                     LEVEL)
 13                         IS NOT NULL)
 14  SELECT LISTAGG (TO_NUMBER (REGEXP_SUBSTR (res, '\d+')), ',')
 15            WITHIN GROUP (ORDER BY 1) result
 16    FROM test_1;

RESULT
--------------------------------------------------------------------------------
123,233,456,983

Report message to a moderator

Re: keep only numeric values in a string [message #654892 is a reply to message #654891]

Tue, 16 August 2016 08:00

Bill B
Messages: 1971
Registered: December 2004

Senior Member

You test isn't correct. He said that the values were in separate records in the details table. You are returning only one row in your "details" table using with. Try the following

WITH details AS (SELECT '123.aa' quantity from dual
                 union all
                 select '233.00' from dual
                 union all
                 select '0456' from dual
                 union all
                 select '983*' FROM DUAL)
SELECT LISTAGG (REGEXP_REPLACE (QUANTITY, '[^0-9]'), ',')
        WITHIN GROUP (ORDER BY REGEXP_REPLACE (QUANTITY, '[^0-9]'))
         STR
   FROM DETAILS;

Report message to a moderator

Re: keep only numeric values in a string [message #654893 is a reply to message #654879]

Tue, 16 August 2016 08:04

Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Hope this is clear enough.

As you can see it is not at all, this is why we repeated you to ALWAYS post a test case.

[Updated on: Tue, 16 August 2016 08:05]

Report message to a moderator

Re: keep only numeric values in a string [message #654894 is a reply to message #654893]

Tue, 16 August 2016 08:06

Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 09 August 2016 08:20

Michel Cadot wrote on Thu, 14 July 2016 20:38

Please read How to use [code] tags and make your code easier to read.
chinmay45 wrote on Thu, 14 July 2016 21:04
Sure will use code tag now onwards
What about your promise?

Barbara Boehmer wrote on Fri, 15 July 2016 01:10
...
You need to provide create table and insert statements and desired results based on the data that you provide. Please see the example that I have provided below.
...
Barbara Boehmer wrote on Fri, 15 July 2016 08:21
You still have not posted create table or insert statements, not even a copy and paste of a select that could be copied and pasted, just images.
chinmay45 wrote on Fri, 15 July 2016 09:11
Thanks Barbara for the help.
Will keep a note of providing detailed info including the create and insert statements from now onward.
What about your promise?

Quote:
This is my create statement, and the insert been done through ETL tool.
And then? Do you think Barbara used your ETL in her previous posts to show the statements?
No, she wrote the INSERT statements, do the same thing and avoid her this work, it is yours.

Report message to a moderator

Re: keep only numeric values in a string [message #654918 is a reply to message #654879]

Tue, 16 August 2016 23:59

Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA

Senior Member

-- test data:

SCOTT@orcl_12.1.0.2.0> select quantity from details
  2  /

QUANTITY
----------
123.aa
233.00
0456
983*

4 rows selected.

-- query:

SCOTT@orcl_12.1.0.2.0> select to_number (regexp_replace (quantity, '[^0-9.]')) from details
  2  /

TO_NUMBER(REGEXP_REPLACE(QUANTITY,'[^0-9.]'))
---------------------------------------------
                                          123
                                          233
                                          456
                                          983

4 rows selected.

Report message to a moderator

Re: keep only numeric values in a string [message #654920 is a reply to message #654879]

Wed, 17 August 2016 01:04

cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA

Senior Member

Hi

Try the below select statement it will remove the special character.

Select TO_NUMBER
          (TRANSLATE
              (:retval,
               '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ~!@#$%^&*()_+}{":?><`=]['''';/,-',
               '1234567890'
              )
          ) convnumber
  From DUAL;

Regards,

C V S

Report message to a moderator

Re: keep only numeric values in a string [message #654924 is a reply to message #654920]

Wed, 17 August 2016 01:28

Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Nope:

SQL> Select TO_NUMBER
  2            (TRANSLATE
  3                (:retval,
  4                 '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ~!@#$%^&*()_+}{":?><`=]['''';/,-',
  5                 '1234567890'
  6                )
  7            ) convnumber
  8    From DUAL;
          (TRANSLATE
           *
ERROR at line 2:
ORA-01722: invalid number

Report message to a moderator

Re: keep only numeric values in a string [message #654925 is a reply to message #654924]

Wed, 17 August 2016 01:34

Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Maybe this one:

SQL> l
  1  WITH details AS (SELECT '123.aa' quantity from dual
  2                   union all
  3                   select '233.00' from dual
  4                   union all
  5                   select '0456' from dual
  6                   union all
  7                   select '983*' FROM DUAL)
  8  Select TO_NUMBER
  9            (TRANSLATE(quantity,
 10               '0'||TRANSLATE(quantity, ' 1234567890.', ' '),
 11               '0')) convnumber
 12* From details
SQL> /
CONVNUMBER
----------
       123
       233
       456
       983

4 rows selected.

Report message to a moderator

Re: keep only numeric values in a string [message #654926 is a reply to message #654925]

Wed, 17 August 2016 01:35

Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> l
  1  WITH details AS (SELECT '123.aa' quantity from dual
  2                   union all
  3                   select '233.89' from dual
  4                   union all
  5                   select '0456' from dual
  6                   union all
  7                   select '983*' FROM DUAL)
  8  Select TO_NUMBER
  9            (TRANSLATE(quantity,
 10               '0'||TRANSLATE(quantity, ' 1234567890.', ' '),
 11               '0')) convnumber
 12* From details
SQL> /
CONVNUMBER
----------
       123
    233.89
       456
       983

4 rows selected.

Report message to a moderator

Previous Topic:	PLS-00231:
Next Topic:	Find unique and sorted field value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 10 18:42:59 CDT 2024