| How to fetch rows having only numbers in a varchar2 column [message #636679] |
Thu, 30 April 2015 04:06  |
 |
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
|
Senior Member |
|
|
I have to retrieve only numbers from the given table , i am using to_number , Since some of the data is having non numeric values , i am not able to use the to_number , how can i resolve this issue.
CREATE TABLE OT_STG ( SLD_CD VARCHAR2(30))
INSERT INTO OT_STG (SLD_CD) VALUES ('10');
INSERT INTO OT_STG (SLD_CD) VALUES ('12');
INSERT INTO OT_STG (SLD_CD) VALUES ('TEST1');
commit;
select * from ot_stg
select to_number(sld_cd) from ot_stg
--required output is
10
20
Lalit : Edited topic title to make it more relevant
[Updated on: Thu, 30 April 2015 05:23] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Overcome ora-01722 error [message #636689 is a reply to message #636679] |
Thu, 30 April 2015 04:36  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Another way using TRANSLATE:
SQL> SELECT * FROM ot_stg WHERE TRANSLATE(sld_cd, '#0123456789','#') IS NULL;
SLD_CD
------------------------------
10
12
SQL>
Perhaps, you have a typo in your desired output, it should be 12 and not 20.
Regards,
Lalit
|
|
|
|
| Re: Overcome ora-01722 error [message #636690 is a reply to message #636687] |
Thu, 30 April 2015 04:38 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
m.abdulhaq wrote on Thu, 30 April 2015 15:01is there any other solution than regexp since it will consume lot of time and i have a big data.
Well, yes REGEX might be slower. But with newer releases and high-end hardware, regex is competing nicely. But yes, I haven't seen a scenario so far where it managed to be faster than non-regex solutions.
|
|
|
|
| Re: Overcome ora-01722 error [message #636696 is a reply to message #636690] |
Thu, 30 April 2015 05:40 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Ok, a small test to compare the timings:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_loops NUMBER := 1000000;
3 l_start NUMBER;
4 l_cnt NUMBER;
5 BEGIN
6 l_start := DBMS_UTILITY.get_time;
7 FOR i IN 1 .. l_loops
8 LOOP
9 SELECT count(*) into l_cnt FROM ot_stg WHERE REGEXP_LIKE(sld_cd, '^\d+$');
10 END LOOP;
11 DBMS_OUTPUT.put_line('REGEX time Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
12
13 l_start := DBMS_UTILITY.get_time;
14 FOR i IN 1 .. l_loops
15 LOOP
16 SELECT count(*) into l_cnt FROM ot_stg WHERE TRANSLATE(sld_cd, '#0123456789','#') IS NULL;
17 END LOOP;
18 DBMS_OUTPUT.put_line('Translate Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
19 END;
20 /
REGEX time Time: 2302 hsecs
Translate Time: 2198 hsecs
PL/SQL procedure successfully completed.
SQL>
Since I am on 12c and you are on 10g, the difference might be more in your version, just like Michel and I discussed here http://www.orafaq.com/forum/mv/msg/196149/631999/#msg_631999
[Updated on: Thu, 30 April 2015 05:40] Report message to a moderator |
|
|
|
|
|
| Re: Overcome ora-01722 error [message #636745 is a reply to message #636689] |
Fri, 01 May 2015 06:15 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 30 April 2015 05:36Another way using TRANSLATE:
Your solution is close but incorrect. It will also return rows where sld_cd is null. You need to add
AND sld_cd IS NOT NULL.
SY.
|
|
|
|
| Re: Overcome ora-01722 error [message #636746 is a reply to message #636745] |
Fri, 01 May 2015 07:05 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@SY,
I completely agree, I missed that.
SQL> insert into ot_stg(sld_cd) values(NULL);
1 row created.
SQL> SELECT *
2 FROM ot_stg
3 WHERE TRANSLATE(sld_cd, '#0123456789','#') IS NULL
4 AND sld_cd IS NOT NULL
5 /
SLD_CD
------------------------------
10
12
SQL> SELECT *
2 FROM ot_stg
3 WHERE TRANSLATE(sld_cd, '#0123456789','#') IS NULL
4 --AND sld_cd IS NOT NULL
5 /
SLD_CD
------------------------------
10
12
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
