February 16, 2002 at 12:31 pm
I have a large number of records that have IP addresses. THey are like 123.45.0.1 to 123.45.0.253 I need to order these but using the traditional order the 200 are next to the 2's. so it would looke like .2 .20 .200 .201 .202 .203 ... to 210.
Is there a way to order them by the last octet only?
Thanks,
Neil
February 16, 2002 at 12:42 pm
this should work
CREATE TABLE #IPS
(
ipvarchar(20)
)
INSERT INTO #IPS
VALUES ('123.45.0.1')
INSERT INTO #IPS
VALUES ('123.45.0.2')
INSERT INTO #IPS
VALUES ('123.45.0.102')
SELECT *
FROM #IPS
ORDER BY CAST (SUBSTRING (ip,10,3) As smallint)
DROP TABLE #IPS
February 16, 2002 at 12:54 pm
I get this error:
Syntax error converting the varchar value '8.3' to a column of data type smallint.
February 16, 2002 at 1:14 pm
One of my employees found this site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7846
It looks interesting. We are going to play with it now...
Thanks,
Neil
February 16, 2002 at 3:09 pm
I'm basing this query on the fact that you know the first 3 octets (and therefore know the length), and you are only trying to sort on the last octet:
SELECT * FROM IP
ORDER BY CAST(RIGHT(IPAddress, LEN(IPAddress) - 9) AS tinyint)
That should work in this specific case. If you need a more general case, either for sorting across a single octet or sorting across all octets, let me know.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 16, 2002 at 8:21 pm
Or this if it varies like my network then.
ORDER BY CAST(RIGHT(IPAddress,(3 - CHARINDEX('.',RIGHT(IPAddress, 3)))) AS tinyint)
February 16, 2002 at 9:10 pm
Or building the full sort:
SELECT IPAddress
FROM IP
ORDER BY
-- First Octet
CAST(LEFT(IPAddress, CHARINDEX('.', IPAddress) - 1) AS tinyint),
-- Second Octet
CAST(SUBSTRING(IPAddress,
CHARINDEX('.', IPAddress) + 1,
CHARINDEX('.', IPAddress,
CHARINDEX('.', IPAddress) + 1)
- CHARINDEX('.', IPAddress) - 1) AS tinyint),
-- Third Octet
CAST(SUBSTRING(IPAddress,
CHARINDEX('.', IPAddress,
CHARINDEX('.', IPAddress) + 1) + 1,
CHARINDEX('.', IPAddress,
CHARINDEX('.', IPAddress,
CHARINDEX('.', IPAddress) + 1) + 1)
- CHARINDEX('.', IPAddress,
CHARINDEX('.', IPAddress) + 1) - 1) AS tinyint),
-- Fourth Octet
CAST(RIGHT(IPAddress,(3 - CHARINDEX('.',RIGHT(IPAddress, 3)))) AS tinyint)
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 02/16/2002 9:19:54 PM
Edited by - bkelley on 02/16/2002 9:20:44 PM
K. Brian Kelley
@kbriankelley
February 17, 2002 at 10:40 am
I had used the last... But I noticed I had some bad data. Some ip had spaces before them and things like that. That is why I was getting those errors. I cleaned up the data and it looks great now. Thanks all!
Neil
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply