Home » SQL & PL/SQL » SQL & PL/SQL » keep only numeric values in a string (oracle 11g, windows)
keep only numeric values in a string [message #654879] Tue, 16 August 2016 06:44 Go to next message
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 Go to previous messageGo to next message
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


Re: keep only numeric values in a string [message #654882 is a reply to message #654880] Tue, 16 August 2016 07:29 Go to previous messageGo to next message
Littlefoot
Messages: 21678
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*').
Re: keep only numeric values in a string [message #654883 is a reply to message #654882] Tue, 16 August 2016 07:30 Go to previous messageGo to next message
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.
Re: keep only numeric values in a string [message #654884 is a reply to message #654883] Tue, 16 August 2016 07:32 Go to previous messageGo to next message
Littlefoot
Messages: 21678
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*
Re: keep only numeric values in a string [message #654885 is a reply to message #654884] Tue, 16 August 2016 07:34 Go to previous messageGo to next message
chinmay45
Messages: 15
Registered: July 2016
Location: bangalore
Junior Member
Agree with you Littlefoot
Re: keep only numeric values in a string [message #654886 is a reply to message #654883] Tue, 16 August 2016 07:34 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Your right, I misread the requirements
Re: keep only numeric values in a string [message #654887 is a reply to message #654886] Tue, 16 August 2016 07:36 Go to previous messageGo to next message
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?
Re: keep only numeric values in a string [message #654889 is a reply to message #654887] Tue, 16 August 2016 07:40 Go to previous messageGo to next message
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
Re: keep only numeric values in a string [message #654890 is a reply to message #654889] Tue, 16 August 2016 07:46 Go to previous messageGo to next message
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;
Re: keep only numeric values in a string [message #654891 is a reply to message #654890] Tue, 16 August 2016 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 21678
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
Re: keep only numeric values in a string [message #654892 is a reply to message #654891] Tue, 16 August 2016 08:00 Go to previous messageGo to next message
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;
Re: keep only numeric values in a string [message #654893 is a reply to message #654879] Tue, 16 August 2016 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
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.

Re: keep only numeric values in a string [message #654918 is a reply to message #654879] Tue, 16 August 2016 23:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.

Re: keep only numeric values in a string [message #654920 is a reply to message #654879] Wed, 17 August 2016 01:04 Go to previous messageGo to next message
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
Re: keep only numeric values in a string [message #654924 is a reply to message #654920] Wed, 17 August 2016 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
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
Re: keep only numeric values in a string [message #654925 is a reply to message #654924] Wed, 17 August 2016 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67887
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.
Re: keep only numeric values in a string [message #654926 is a reply to message #654925] Wed, 17 August 2016 01:35 Go to previous message
Michel Cadot
Messages: 67887
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.
Previous Topic: PLS-00231:
Next Topic: Find unique and sorted field value
Goto Forum:
  


Current Time: Mon Jun 21 17:32:35 CDT 2021