# Can someone explain this SQL query?

Discussion in 'OT Technology' started by SubOptimal, Feb 12, 2010.

1. ### SubOptimalNew Member

Joined:
Jun 27, 2002
Messages:
4,410
0
Can someone explain this SQL query?

Code:
sql> SELECT rowid, intIP,
...>   ((intIP >> 24) & 255) ||'.'||
...>   ((intIP >> 16) & 255) ||'.'||
...>   ((intIP >>  8) & 255) ||'.'||
...>   ((intIP      ) & 255) AS strIP
...> FROM IP_table;

It's used to convert IP integers (e.g. 3232235777) to IP addresses (192.168.1.1). The only part of it I understand are the "SELECT rowid, intIP", || means concat, and from table.

2. ### mobbarleyActive Member

Joined:
Mar 4, 2005
Messages:
9,256
2
Location:
Sydney
'>>' is binary shift right, '& 255' is a binary and mask

Joined:
Mar 4, 2005
Messages:
9,256
2
Location:
Sydney
4. ### mobbarleyActive Member

Joined:
Mar 4, 2005
Messages:
9,256
2
Location:
Sydney
the number '3232235777' (i havent checked) should be the 4x 8bit segments of the ip address:

these groups bits are shifted right to the RHS 8 bits and then the rest are removed by the AND operation.

5. ### SubOptimalNew Member

Joined:
Jun 27, 2002
Messages:
4,410
0
It is, I converted 192.168.1.1 to that integer, I'm just having problems understanding that SQL query to convert it back. I'm kind of familiar with bitmasks, but I've never heard of binary shifting.

So to get the first number of 192, you shift 24 bits to the right like in the SQL query and add a mask 0f 255...

Code:
11000000 10101000 00000001 00000001 = 192.168.1.1 or 3232235777
00000000 00000000 00000000 11111111 = bitmask of 255
__________________________________
__________________________________1

It's 1, instead of 192? What am I doing wrong?

Last edited: Feb 12, 2010
6. ### CodeXGuest

You didn't do the shift...

You would get

11000000 10101000 00000001 00000001 = 192.168.1.1
>> 24
11111111 11111111 11111111 11000000 = 255.255.255.192
AND
00000000 00000000 00000000 11111111 = 0.0.0.255
=
---------------------------------------
00000000 00000000 00000000 11000000 = 0.0.0.192 (or simply 192 as decimal)

Last edited by a moderator: Feb 12, 2010
7. ### CodeXGuest

When you right shift a signed value it preserves the sign bit, so if the highest order bit was a 1, a 1 will be shifted in.

I have no idea why this would be a signed number if it is representing an IP address... if it is unsigned then the AND mask is unnecessary as all 0's will be shifted in.

Joined:
Jun 27, 2002
Messages:
4,410