Ordereing an IP address

Question

Post reply

Ordereing an IP address

hb21l6

SSCommitted

Points: 1647
More actions
April 29, 2008 at 1:42 am

#72020

hello all,
Is there a way to order on an nvarchar field which contains an IP address,
192.168.215.1
192.168.23.1
192.168.5.1
172.16.9.45
etc
Currently it orders based on text and not integer

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

w.lengenfelder SSC Eights! Points: 884 More actions · Answer 1

Hello,

here is a sample how to solve this

create function dbo.iptext2no(@iptext varchar(16))

returns bigint

as

begin

declare @pPosFrom int, @pPosTo int, @pWorkstring varchar(16)

set @pPosFrom = 0

set @pWorkstring = ''

while @pPosFrom < len(@iptext)+1

begin

set @pPosTo = charindex('.',@iptext,@pPosFrom)

if @pPosTo < 1

begin

set @pWorkstring = @pWorkstring + right('00' + substring(@iptext,@pPosFrom,len(@iptext)-@pPosFrom+1),3)

set @pPosFrom = len(@iptext) + 1

end else begin

set @pWorkstring = @pWorkstring + right('00' + substring(@iptext,@pPosFrom,@pPosTo-@pPosFrom),3)

set @pPosFrom = @pPosTo+1

end

return convert(bigint, @pWorkstring)

end

go

drop table ipaddress

create table ipaddress (

iptext varchar(16)

)

go

insert into ipaddress(iptext) values('192.168.215.1')

insert into ipaddress(iptext) values('192.168.23.1')

insert into ipaddress(iptext) values('192.168.5.1')

insert into ipaddress(iptext) values('172.16.9.45')

select iptext, dbo.iptext2no(iptext) ipNo from ipaddress

Regards

w. lengenfelder

Sergiy SSC Guru Points: 110206 More actions · Answer 2

Store IP address as int or binary(4).

Convert it to varchar on SELECT using UDF and use value stored in column for ORDER BY clause.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

This will do it...

--===== Create a table to demo with

DECLARE @DemoTable TABLE (IPAddr VARCHAR(15))

INSERT INTO @DemoTable (IPAddr)

SELECT '192.168.215.1' UNION ALL

SELECT '192.168.23.1' UNION ALL

SELECT '192.168.5.1' UNION ALL

SELECT '172.16.9.45'

--===== Do the sort

SELECT *

FROM @DemoTable

ORDER BY STR(PARSENAME(IPaddr,4),3),

STR(PARSENAME(IPaddr,3),3),

STR(PARSENAME(IPaddr,2),3),

STR(PARSENAME(IPaddr,1),3)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hb21l6 SSCommitted Points: 1647 More actions · Answer 4

thanks for the replys guys.

this is the solution i came up with in the end, some borrorwed some new.

SELECT * tbl_locations

WHERE CompanyName = 'someCompanyName'

ORDER BY CONVERT(int, REPLACE(CONVERT(nvarchar, SUBSTRING(IP, 1, 3)), '.', '')), CONVERT(int, REPLACE(CONVERT(nvarchar, SUBSTRING(IP,

PATINDEX('%.%.%', IP) + 1, 3)), '.', '')), CONVERT(int, REPLACE(CONVERT(nvarchar, SUBSTRING(SUBSTRING(IP, 5, 11), PATINDEX('%.%.%',

SUBSTRING(IP, 5, 11)) + 1, 3)), '.', '')), CONVERT(int, SUBSTRING(RIGHT(RTRIM(IP), 3), CHARINDEX('.', RIGHT(RTRIM(IP), 3)) + 1, 3)) desc

Thanks anyway

Dave

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (4/29/2008)
This will do it...
--===== Create a table to demo with
DECLARE @DemoTable TABLE (IPAddr VARCHAR(15))
INSERT INTO @DemoTable (IPAddr)
SELECT '192.168.215.1' UNION ALL
SELECT '192.168.23.1' UNION ALL
SELECT '192.168.5.1' UNION ALL
SELECT '172.16.9.45'
--===== Do the sort
SELECT *
FROM @DemoTable
ORDER BY STR(PARSENAME(IPaddr,4),3),
STR(PARSENAME(IPaddr,3),3),
STR(PARSENAME(IPaddr,2),3),
STR(PARSENAME(IPaddr,1),3)

Heh. Cute trick, I have to remember this one.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

hb21l6 (4/29/2008)
thanks for the replys guys.
this is the solution i came up with in the end, some borrorwed some new.
SELECT * tbl_locations
WHERE CompanyName = 'someCompanyName'
ORDER BY CONVERT(int, REPLACE(CONVERT(nvarchar, SUBSTRING(IP, 1, 3)), '.', '')), CONVERT(int, REPLACE(CONVERT(nvarchar, SUBSTRING(IP,
PATINDEX('%.%.%', IP) + 1, 3)), '.', '')), CONVERT(int, REPLACE(CONVERT(nvarchar, SUBSTRING(SUBSTRING(IP, 5, 11), PATINDEX('%.%.%',
SUBSTRING(IP, 5, 11)) + 1, 3)), '.', '')), CONVERT(int, SUBSTRING(RIGHT(RTRIM(IP), 3), CHARINDEX('.', RIGHT(RTRIM(IP), 3)) + 1, 3)) desc
Thanks anyway
Dave

I'm thinking you should reconsider 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 7

Tell me I'm wrong.. if I am.. but isn't this the simplest way?

-===== Create a table to demo with

DECLARE @DemoTable TABLE (IPAddr VARCHAR(15))

INSERT INTO @DemoTable (IPAddr)

SELECT '192.168.215.1' UNION ALL

SELECT '192.168.23.1' UNION ALL

SELECT '192.168.5.1' UNION ALL

SELECT '172.16.9.45'

-- Sort

SELECT *

FROM @DemoTable

ORDER BY CAST(replace (IPAddr,'.','') as int)

hb21l6 SSCommitted Points: 1647 More actions · Answer 8

Appologies guys,

I only needed a SQL line to run from an ASP page, I didn't want to do this through an SP as there is more going on with the SQL line other then what I posted.

thanks again

Dave

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 9

The table variable is only created for demo purposes..

Just use ORDER BY CAST(replace (IPAddr,'.','') as int)

StarNamer SSCrazy Eights Points: 8744 More actions · Answer 10

janine.rawnsley (4/30/2008)
The table variable is only created for demo purposes..
Just use ORDER BY CAST(replace (IPAddr,'.','') as int)

No, because this would place '192.1.1.1' before '19.2.1.12'.

Also addresses like '192.168.123.234' would cause an integer overflow when you tried to cast/convert it.

Derek

janine.rawnsley Ten Centuries Points: 1056 More actions · Answer 11

Derek Dongray (4/30/2008)
janine.rawnsley (4/30/2008)
The table variable is only created for demo purposes..
Just use ORDER BY CAST(replace (IPAddr,'.','') as int)
No, because this would place '192.1.1.1' before '19.2.1.12'.
Also addresses like '192.168.123.234' would cause an integer overflow when you tried to cast/convert it.

Do'h.. a bit more test data (or coffee) and that would have jumped out at me.

StarNamer SSCrazy Eights Points: 8744 More actions · Answer 12

janine.rawnsley (4/30/2008)
Do'h.. a bit more test data (or coffee) and that would have jumped out at me.

It took me 2 attempts as the example was originally going to be '192.001.001.001' which is legal, but rather obscure syntax!:D

I'm just impressed by the unusual use of PARSENAME. I had to look up in BOL what it was supposed to do (which is to parse the components of an object name of the form [server].[database].[schema].[object]).

Derek

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

hb21l6 (4/30/2008)
Appologies guys,
I only needed a SQL line to run from an ASP page, I didn't want to do this through an SP as there is more going on with the SQL line other then what I posted.
thanks again
Dave

So, do the IP addresses live in a table or not? Where do they live?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 14

Jeff Moden (4/29/2008)
This will do it...
--===== Create a table to demo with
DECLARE @DemoTable TABLE (IPAddr VARCHAR(15))
INSERT INTO @DemoTable (IPAddr)
SELECT '192.168.215.1' UNION ALL
SELECT '192.168.23.1' UNION ALL
SELECT '192.168.5.1' UNION ALL
SELECT '172.16.9.45'
--===== Do the sort
SELECT *
FROM @DemoTable
ORDER BY STR(PARSENAME(IPaddr,4),3),
STR(PARSENAME(IPaddr,3),3),
STR(PARSENAME(IPaddr,2),3),
STR(PARSENAME(IPaddr,1),3)

I have been doing this:

SELECT *

FROM @DemoTable

ORDER BY CAST(PARSENAME(IPaddr, 4) AS INT)

,CAST(PARSENAME(IPaddr, 3) AS INT)

,CAST(PARSENAME(IPaddr, 2) AS INT)

,CAST(PARSENAME(IPaddr, 1) AS INT)

Is Jeff's way better for performance? I tried looking at the query plans but I couldn't tell.