problem with the IP range (CString) selection

[kaya17]

Dear all,
i have a question of the select statement for the IP address.

my selection query is as below:
select* from ip_table where ip <='121.121.22.254' and ip>='121.121.22.1"

the ip value is stored as text in MS Access.

but the ip values like 30+, 40+, 50+...99 are not returned.

that is seems that the sql interpretor only compares by each character, it will look 33 bigger than 254.

can anybody help me with this?
really thanks a lot!

kaya

 

[PHV]

Have you tried something like this ?
select * from ip_table where ip Like '121.121.22.%' and ip not in ('121.121.22.0','121.121.22.255')


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kaya17]

but the point here is the user knows nothing about the SQL, and what he knows is just to key in an IP range.

for example:
for the range 111.111.111.65~111.111.115.86

"like" operator will not be possible here.

and what i can think of is to do a consecutive selection:
that is to break it into several ranges:
111.111.111.65-111.111.111.99
111.111.111.100-111.111.111.199
111.111.111.200-111.111.111.254
....
and repeat it again till 111.111.115.86

that means i have to convert each octet to int, and find the difference between the corresponding octet and do a loop to check, and append the results to the list(to show them to the user).

the problem here is how to convert the string to int?

anyone knows about it?
Really thanks for your patience to read and to reply if you got any other suggestions.
thanks!

kaya

 

[r937]

it would be really nice if you could store the integer value of each IP

then when the user submits a pair of IP numbers for your range test, all you have to do is convert each to an integer and then the query will be amazingly fast:

... where IPcol between int1 and int2

of course, converting the IP numbers to integers in the first place is the problem, no?

this is best handled in your scripting language

you could do it with substring and instring functions, but i advise against it

i've done it, and it is exceedingly complex and ugly, and looks something like this (ms access sysntax):

select ipno
 , right('00'&mid(ipno
  ,1
  ,instr(ipno,'.')-1)
   , 3 )
 , right('00'&mid(ipno
  ,instr(ipno,'.')+1 
  ,instr(mid(ipno
       ,instr(ipno,'.')+1)
        ,'.')-1)
   , 3 )
 , right('00'&mid(ipno
  ,instr(mid(ipno
       ,instr(ipno,'.')+1)
        ,'.')
  +instr(ipno,'.')+1
  ,instr(mid(ipno
       ,instr(mid(ipno
            ,instr(ipno,'.')+1)
        ,'.')
       +instr(ipno,'.')+1)
        ,'.')-1)
   , 3 )
 , right('00'&mid(ipno
  ,instr(mid(ipno
       ,instr(mid(ipno
            ,instr(ipno,'.')+1)
        ,'.')
       +instr(ipno,'.')+1)
        ,'.') 
  +instr(mid(ipno
       ,instr(ipno,'.')+1)
        ,'.')
  +instr(ipno,'.')+1)
  , 3 )
from iptable

how much nicer it would be to do this in a scripting language that has list functions (using the dot as a list item separator)



rudy

http://r937.com/

SQL Consulting

 

[kaya17]

thanks for your reply. but instr() is not a function of MFC.

 

[r937]

MFC? what's that?

i did say it was ms access syntax, didn't i

rudy

http://r937.com/

SQL Consulting