IP sorting problem

Wednesday, March 29. 2006

In one of the projects, we store the client's IP address (for audit purpose) as varchar. Therefore by default if we sort them using SQL, "60.1.1.1" comes after "101.1.1.1". Unfortunately we are requested to sort the IP addresses like number, means 60.1.1.1 followed by 101.1.1.1. If you've encountered this issue before, kindly share me some ideas.

Posted by Steven Yong in computer at 16:00 | Comments (6) | Trackbacks (0)

...

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

Hmmm....

I had the same problem with time. We had to store it in HHMMSS format and so, we always had a number like 11005 which is 01:10:05am in our database. Looks weird but at least it sorts in the right order, where 11005 would come before 100105 which is 10:01:05am.

Anyway, wonder if it would be good to use SQL string functions to substring the first number and sort by that number? That is, if you substring out 101 and 60 and converted that to integer, 60 would come before 101, right?

Its a bit tedious on the SQL side but I think it just might work....

#1 Angie (Homepage) on 2006-03-30 09:50 (Reply)

Sad to tell that the way the IP is stored probably can't be changed. So, very likely "SQL string functions" would be the solution, but not only the first number has to be sorted.

#2 Steven (Homepage) on 2006-03-30 13:31 (Reply)

Yea... That can be a problem.

Yea, you're right, all the substring numbers need to be sorted as well.

It would be nice if the columns could be split out to its individual components but then, it would not be portable.

Anyway, hope you find a solution soon!

#2.1 Angie (Homepage) on 2006-03-30 15:35 (Reply)

If not wrong, for ip address, the max it can only be 3 digits in between every .
i.e the it can only be xxx.xxx.xxx.xxx, but not xxxx.xxxx.xxxx.xxxx

I suggest when you store the ip into the table, if the it's not 3 digit, then u prefix the rest with 0 (zero). so that the ip will look like:
"60.1.1.1" -> "060.001.001.001"
"101.1.1.1" -> "101.001.001.001"

Then when you sort them using SQL, "060.001.001.001" will come before "101.001.001.001".

And after sorting if you want to display the ip, then you may just trim the leading zeros for each element, so that you will get back 60.1.1.1 and display.

Hope it helps

#3 Siow Ching on 2006-03-30 17:27 (Reply)

SELECT test1
FROM test
ORDER BY REPLACE(TRANSLATE(upper(test1), '0123456789', '0'), '0'), TO_NUMBER(REPLACE(TRANSLATE(upper(test1), TRANSLATE(upper(test1), '0123456789', RPAD('*', 10, '*')), ' '), ' '))

my fault my fault, I should have mentioned what DB I'm using for that problem. This morning my former coworker (I call her sis), an oracle expert was sending me this tips via YM. I believe it'd be useful if I'm using oracle, but her help is really appreciated. By this far I find siow ching's idea makes more sense to me, thanks siow ching.
Will keep you updated about the ultimate solution. (I'm using informix)

#4 Steven (Homepage) on 2006-04-04 09:53 (Reply)

ultimate solution - managed to convince customers that the IP should be treated as text instead of number

#5 steven (Homepage) on 2006-07-13 10:07 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images.
	Remember Information? Subscribe to this entry