Home » SQL & PL/SQL » SQL & PL/SQL » String to Number Problem
String to Number Problem [message #432888] Fri, 27 November 2009 01:23 Go to next message
ramr_Sw
Messages: 31
Registered: October 2009
Member
Oracle 8i
test table
code item level_no
1     a    11 varchar
2     a    12 varchar
3     a    X varchar
4     a    14 varchar
5     a    15 varchar
6     a    16 varchar
7     a    Y varchar
8     a    18 varchar

Level_no field is declared as the varchar and I want to display the result where level_no < 15

WHERE to_number(REGEXP_REPLACE(level,'^.*\D.*$')) < 15

when I used above regexp_replace,but got error message says "regexp_replace Invalid Identifier"

IS there any way to find with out using regexp_replace

Pls help me

Thanks in advance

[Mod-Edit: Frank added code-tags around table to improve readability]

[Updated on: Fri, 27 November 2009 01:48] by Moderator

Report message to a moderator

Re: String to Number Problem [message #432889 is a reply to message #432888] Fri, 27 November 2009 01:35 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As you are using 8i.Which is no longer supported...
REGEXP_REPLACE which is available from 10g.

Sriram Smile

[Updated on: Fri, 27 November 2009 01:45]

Report message to a moderator

Re: String to Number Problem [message #432890 is a reply to message #432888] Fri, 27 November 2009 01:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can write a function that checks if the entered parameter is numeric or not.
Search this forum for is_numeric for some examples.
Re: String to Number Problem [message #432927 is a reply to message #432888] Fri, 27 November 2009 06:24 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Frank Sir has already provided the solution
ved@oralin> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION is_number(char_in VARCHAR2) RETURN number IS
  2   n NUMBER;
  3  BEGIN
  4    n := TO_NUMBER(char_in);
  5    RETURN 1;
  6  EXCEPTION
  7    WHEN OTHERS THEN
  8      RETURN 0;
  9* END is_number;
ved@oralin> /

Function created.

Elapsed: 00:00:00.00
ved@oralin> select is_number(level_no) from t100;

IS_NUMBER(LEVEL_NO)
-------------------
                  1
                  1
                  0
                  1
                  1
                  0

6 rows selected.



The other alternative is using TRIM/TRANSLATE/LENGTH.But you should follow the above one

SELECT BANNER FROM V$VERSION;

BANNER                                                                                                                              
----------------------------------------------------------------                                                                    
Personal Oracle9i Release 9.2.0.1.0 - Production                                                                                    
PL/SQL Release 9.2.0.1.0 - Production                                                                                               
CORE	9.2.0.1.0	Production                                                                                                           
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production                                                                              
NLSRTL Version 9.2.0.1.0 - Production                                                                                               

Elapsed: 00:00:00.00
ved@oralin> select code,level_no from (
  2  
ved@oralin> select * from t100;

      CODE LEVEL_NO                                                                                                                 
---------- ----------                                                                                                               
         1 11                                                                                                                       
         2 12                                                                                                                       
         3 x                                                                                                                        
         4 14                                                                                                                       
         5 15                                                                                                                       
         6 abc1                                                                                                                     

6 rows selected.

Elapsed: 00:00:00.00
ved@oralin> SELECT code,nvl(length(trim(TRANSLATE(LEVEL_NO, '0123456789',' '))),0) col1,level_no
  2          FROM t100;

      CODE       COL1 LEVEL_NO                                                                                                      
---------- ---------- ----------                                                                                                    
         1          0 11                                                                                                            
         2          0 12                                                                                                            
         3          1 x                                                                                                             
         4          0 14                                                                                                            
         5          0 15                                                                                                            
         6          3 abc1                                                                                                          

6 rows selected.

Elapsed: 00:00:00.01
ved@oralin> SELECT code,nvl(length(trim(TRANSLATE(LEVEL_NO, '0123456789',' '))),0) col1,level_no from t100;

      CODE       COL1 LEVEL_NO                                                                                                      
---------- ---------- ----------                                                                                                    
         1          0 11                                                                                                            
         2          0 12                                                                                                            
         3          1 x                                                                                                             
         4          0 14                                                                                                            
         5          0 15                                                                                                            
         6          3 abc1                                                                                                          

6 rows selected.

Elapsed: 00:00:00.01
--From here put your logic to get those rows where level_no<15

ved@oralin> spool off





Regards,
Ved

[Updated on: Fri, 27 November 2009 06:36]

Report message to a moderator

Re: String to Number Problem [message #432928 is a reply to message #432927] Fri, 27 November 2009 06:30 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:

Personal Oracle9i Release 9.2.0.1.0


OP using Oracle 8i not 9i

sriram Smile
Re: String to Number Problem [message #432929 is a reply to message #432928] Fri, 27 November 2009 06:41 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
ramoradba wrote on Fri, 27 November 2009 06:30
Quote:

Personal Oracle9i Release 9.2.0.1.0


OP using Oracle 8i not 9i

sriram Smile


Not sure what function(that is being used in 2nd sql) you think that does not support in 8i

http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm

[Updated on: Fri, 27 November 2009 06:44]

Report message to a moderator

Re: String to Number Problem [message #432932 is a reply to message #432929] Fri, 27 November 2009 06:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i apologize ...Miss read the post. Smile

sriram Smile
Re: String to Number Problem [message #432936 is a reply to message #432927] Fri, 27 November 2009 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Frank Sir has already provided the solution

So why do you repeat it? Let OP learn and search by itself. You don't need to spoonfeed him (once more).

Regards
Michel
Re: String to Number Problem [message #432938 is a reply to message #432927] Fri, 27 November 2009 08:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Its_me_ved wrote on Fri, 27 November 2009 13:24

[code] 1 CREATE OR REPLACE FUNCTION is_number(char_in VARCHAR2) RETURN number IS
2 n NUMBER;
3 BEGIN
4 n := TO_NUMBER(char_in);
5 RETURN 1;
6 EXCEPTION
7 WHEN OTHERS THEN
8 RETURN 0;
9* END is_number;
ved@oralin> /


If you would simply return the number for numbers and NULL for non-numbers, you can make it all a lot easier, especially since the original question was that rows with a value < 15 have to be selected.
Re: String to Number Problem [message #432953 is a reply to message #432938] Fri, 27 November 2009 10:55 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for the suggesion Sir.


@ Sir (Michel),
This is a FAQ.Frank Sir has asked the OP to search this forum for is_numeric for some examples.

I wanted to provide the other alternative saying that we can also do that without creating a pl/sql.But still it was not a complete one.

Regards,
Ved





[Updated on: Fri, 27 November 2009 10:59]

Report message to a moderator

Re: String to Number Problem [message #432957 is a reply to message #432953] Fri, 27 November 2009 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is a FAQ.Frank Sir has asked the OP to search this forum for is_numeric for some examples.

So what did you post it?

Quote:
wanted to provide the other alternative saying that we can also do that without creating a pl/sql.But still it was not a complete one.

OK but why did you post the first part?

Regards
Michel
Re: String to Number Problem [message #432963 is a reply to message #432957] Fri, 27 November 2009 12:38 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

Quote:
wanted to provide the other alternative saying that we can also do that without creating a pl/sql.But still it was not a complete one.
OK but why did you post the first part?


Thinking that the OP would not live with that alternative solution only if he was unable to figure out what Frank sir has said! So better to be both one...


Regards,
Ved
Re: String to Number Problem [message #432964 is a reply to message #432963] Fri, 27 November 2009 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if he was unable to figure out what Frank sir has said

Michel Cadot wrote on Fri, 27 November 2009 15:08
...
Let OP learn and search by itself. You don't need to spoonfeed him (once more).
...

If he is unable to find a FAQ with this clue then he will never be able to do his job, so better not telling him how to do it or will you always be there to do his job?

Regards
Michel

Re: String to Number Problem [message #432966 is a reply to message #432964] Fri, 27 November 2009 12:56 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Sir,
I had to provide the function thinking that some people are happy getting ready made solution and just ignore the other.I have seen so many such instances. I just did NOT wanted him to ignore Frank Sir's solution. Thats all! My apologies!!!!!!
Re: String to Number Problem [message #432969 is a reply to message #432966] Fri, 27 November 2009 14:29 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you all, please, stop this useless discussion in public? I believe that we all know what's being said so there's no need to continue any further with this off-topic debate. Though, you may chat in private as long as you wish.
Re: String to Number Problem [message #433074 is a reply to message #432888] Sun, 29 November 2009 13:47 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I like it when we get Knighted.

since the cat is out of the bag, anyone have comments about this one?

in ('1','2','3','4'...,'15')


Kevin
Previous Topic: Inner query return different results when i wrap it up
Next Topic: Left outer join with NVL and Where
Goto Forum:
  


Current Time: Wed Sep 28 10:50:14 CDT 2016

Total time taken to generate the page: 0.11838 seconds