Home » SQL & PL/SQL » SQL & PL/SQL » Difference of between clause in number&varchar2 fields
Difference of between clause in number&varchar2 fields [message #253014] Sat, 21 July 2007 08:51 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
I was using between clause in a varchar2 field.
I tried the query
select * from test_towns where town_id between '9' and '19';
Here town_id is varchar2 field(It can't be a number field as it could contain alphabets and other characters) and I have no rows returned even though I have values such as 10,15 etc in that field.
If it were a number field, it would have been worked.
But with the character field itself how will I form the query?
Re: Difference of between clause in number&varchar2 fields [message #253015 is a reply to message #253014] Sat, 21 July 2007 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>But with the character field itself how will I form the query?
With some difficulty because '9' is GREATER than '1'
For it to work as you expect it to work, you would have stored '09' instead of '9'.
Re: Difference of between clause in number&varchar2 fields [message #253016 is a reply to message #253015] Sat, 21 July 2007 09:30 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
It seems to be not practical in my case as it won't work for all data, to store'0' in front of it.
Am I better of converting town_id to number and then apply between clause?
My aim is to delete data from a table based on various conditions and this between clause is among one of the conditions.But I don't know what if the data is '3C' for example?(and i am checking whether it is between 3A and 3E)
Re: Difference of between clause in number&varchar2 fields [message #253020 is a reply to message #253014] Sat, 21 July 2007 09:36 Go to previous message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
The use of BETWEEN on VARCHAR2 datatypes is useful only when both variables are the same length.
Previous Topic: only answer my question if you have nothing better to do
Next Topic: procedure result inserting into file now want to as result set
Goto Forum:
  


Current Time: Wed Dec 07 14:55:25 CST 2016

Total time taken to generate the page: 0.10689 seconds