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 |
|
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 #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;
|
|
|
Re: keep only numeric values in a string [message #654891 is a reply to message #654890] |
Tue, 16 August 2016 07:55 |
|
Littlefoot
Messages: 21811 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 |
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 #654894 is a reply to message #654893] |
Tue, 16 August 2016 08:06 |
|
Michel Cadot
Messages: 68665 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:04Sure 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:21You 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:11Thanks 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 #654920 is a reply to message #654879] |
Wed, 17 August 2016 01:04 |
|
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 #654926 is a reply to message #654925] |
Wed, 17 August 2016 01:35 |
|
Michel Cadot
Messages: 68665 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.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:15:36 CDT 2024
|