How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order

How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order

• John Ketchpaw

  Use PARSENAME

  example:

  select * from(
  select '0.0.18.1' as IpAddress union all
  select '0.1.1.2' union all
  select '0.0.0.1' ) z
  order by parsename(ipaddress,1),
  parsename(ipaddress,2),
  parsename(ipaddress,3),
  parsename(ipaddress,4)

  Denis The SQL Menace
  SQL blog:http://sqlservercode.blogspot.com/

  
  

• Netasia

  Ah ok. Sorry now I get the idea.

  I have a solution but you need to have fixed length of the IP address field. It will be much easier to sort without using Stored Proc

  ip must be in xxx.xxx.xxx.xxx format

  For the data given below in table test:

  IP

  -----------------------------
  000.000.000.001
  000.000.001.002
  000.000.002.001
  000.000.018.001
  000.001.001.002

  try using

  SELECT IP,
  SUBSTRING(IP,1,3) AS IP1,
  SUBSTRING(IP,5,3) AS IP2,
  SUBSTRING(IP,9,3) AS IP3,
  SUBSTRING(IP,13,3) AS IP4

  FROM test
  ORDER BY
  SUBSTRING(IP,13,3) ASC,
  SUBSTRING(IP,9,3) ASC,
  SUBSTRING(IP,5,3) ASC,
  SUBSTRING(IP,1,3) ASC

  When you run this query the result will be:

  ip IP1 IP2 IP3 IP4

  -----------------------------------------------------------

  000.000.000.001 000 000 000 001
  000.000.002.001 000 000 002 001
  000.000.018.001 000 000 018 001
  000.000.001.002 000 000 001 002
  000.001.001.002 000 001 001 002

  Hope this helps.

  
  

• CISCBrain

  Modify from Louis's code to convert the octave to int so that 0.0.2.1 comes before 0.0.18.1
  
  create table ipAddress
  (
  ipAddress nvarchar(20)
  )
  insert into ipAddress
  select '0.0.18.1' union all
  select '0.1.1.2' union all
  select '0.0.0.1' union all
  select '0.0.2.1'
  
  go
  select oct1, oct2, substring(ipAddress,1,charindex('.',ipAddress) - 1) + 0 as oct3,
  substring(ipAddress,charindex('.',ipAddress) + 1,len(ipAddress)) + 0 as oct4
  from
  (
  select oct1,
  substring(ipAddress,1,charindex('.',ipAddress) - 1) + 0 as oct2,
  substring(ipAddress,charindex('.',ipAddress) + 1, len(ipAddress)) as ipAddress
  from
  (
  select substring(ipAddress,1,charindex('.',ipAddress) - 1) + 0 as oct1,
  substring(ipAddress,charindex('.',ipAddress) + 1,len(ipAddress)) as ipAddress
  from ipAddress
  ) as derivedTable1
  ) as derivedTable2
  order by 1,2,3,4
  go
  drop table ipAddress
  
  

• ScofferX

  Or to split names

  declare @name varchar(49)
  select @name ='Bill Gates'

  
  select PARSENAME(REPLACE(@name,' ','.'),2) as FirstName,
  PARSENAME(REPLACE(@name,' ','.'),1) as LastName

  Denis the SQL Menace

  http://sqlservercode.blogspot.com/

  
  

• Rajesh G

  Funny you should ask such a question. This is exactly what my blog from last night was about. A value stored in a varchar column with more than one distinct part of the same value (I called it a multi purpose character column). Any time you need to deal with one part of a value as different than the other, in this case for a sort, you need to rethink your storage. In this case, there are two good ways to store ipAddresses, as 1 bigint or 4 tinyint values. Check this article for a more complete reference: http://www.aspfaq.com/show.asp id=2450

  Ah, but I still love SQL tricks. What I did to solve this was to peel the onion and do a substring for the first octet, then the "rest" of the string. Then I put that in a derived table, and did it again, and then again for the last one.

  You probably could use a CTE to do this recursively, but since all ipAddresses (until ipv6 gets popular) are 4 parts, then this is a fine enough way to do this without using a function or procedure, as you requested

  create table ipAddress
  (
  ipaddress nvarchar(20)
  )
  insert into ipAddress
  select '0.0.18.1'
  union all
  select '0.1.1.2'
  union all
  select '0.0.0.1'
  go
  select oct1, oct2, substring(ipAddress,1,charindex('.',ipaddress) - 1) as oct3,
  substring(ipAddress,charindex('.',ipaddress) + 1,len(ipAddress)) as oct4
  from (select oct1, substring(ipAddress,1,charindex('.',ipaddress) - 1) as oct2,
  substring(ipAddress,charindex('.',ipaddress) + 1,len(ipAddress)) as ipaddress
  from (
  select substring(ipAddress,1,charindex('.',ipaddress) - 1) as oct1,
  substring(ipAddress,charindex('.',ipaddress) + 1,len(ipAddress)) as ipaddress
  from ipAddress) as derivedTable1) as derivedTable2
  order by 1,2,3,4
  go
  drop table ipAddress

  
  
  

• no1cyf

  but this will do a string sorting.
  

• jquevedog

  PARSENAME! What the heck is that I had never heard of that function :) Very cool.
  
  

• _cham

  You have to cast the parts to integer. The parsename value will simply be the text of the number, and sorting is from left to right so 1 is lower than 2 in this case. Look again at Steve's reply:

  PARSENAME is well-suited for this:
  
  select ipAddress
  from netComputers
  order by
  CAST(PARSENAME(ipAddress,4) AS INT),
  CAST(PARSENAME(ipAddress,3) AS INT),
  CAST(PARSENAME(ipAddress,2) AS INT),
  CAST(PARSENAME(ipAddress,1) AS INT)
  
  -- Steve Kass
  -- Drew University
  

  
  
  

• peter_akerstrom

  Yes. But it will give you your expected result based on your given sample data.
  

• Will Perry

  dpeeth wrote:

  How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order without using stored procedure Ex: Table: netComputers(3 rows) Column Name: ipAddress (string data type) ipAddress 0.0.18.1 0.1.1.2 0.0.0.1 Sql query : if I use the query Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc Gives result as : ipAddress 0.0.0.1 0.1.1.2 0.0.18.1 Where as expected result should be: ipAddress 0.0.0.1 0.0.18.1 0.1.1.2

  You converted your data to numeric format. When you use this select statement, you are sorting numeric data.

  Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc

  Try using:

  Select ipAddreaa from netComputers Order by ipAdddress Asc

  
  

• Vaibhav2007

  PARSENAME is well-suited for this:
  
  select ipAddress
  from netComputers
  order by
    CAST(PARSENAME(ipAddress,4) AS INT),
    CAST(PARSENAME(ipAddress,3) AS INT),
    CAST(PARSENAME(ipAddress,2) AS INT),
    CAST(PARSENAME(ipAddress,1) AS INT)
  
  -- Steve Kass
  -- Drew University
  
  
  dpeeth@discussions.microsoft.com wrote:
  
  > How to sort table in sql2000 with ipaddress(format x.x.x.x) as column
  > with nvarchar datatype in ascending order
  > 
  > without using stored procedure
  > 
  > 
  > 
  > Ex: 
  > 
  > 
  > 
  > Table: netComputers(3 rows)
  > 
  > Column Name: ipAddress (string data type) 
  > 
  > 
  > 
  > ipAddress
  > 
  > 0.0.18.1
  > 
  > 0.1.1.2
  > 
  > 0.0.0.1
  > 
  > 
  > 
  > Sql query : if I use the query 
  > 
  > 
  > 
  > Select ipAddress from netComputers order by cast(
  > replace(ipaddress,'.','') as numeric(12)) asc
  > 
  > 
  > 
  > Gives result as : 
  > 
  > ipAddress
  > 
  > 0.0.0.1
  > 
  > 0.1.1.2
  > 
  > 0.0.18.1
  > 
  > 
  > 
  > Where as expected result should be: 
  > 
  > 
  > 
  > ipAddress
  > 
  > 0.0.0.1
  > 
  > 0.0.18.1
  > 
  > 0.1.1.2
  > 
  > 
  > 
  > 
  

  
  

• rikimaruXP

  You can use PARSENAME in a lot of situations, for example this one, getting all the data up until the minus sign

  CREATE TABLE #Test (
  SomeField VARCHAR(49))
  
  INSERT INTO #Test
  VALUES ('aaa-bbbbb')
  
  INSERT INTO #Test
  VALUES ('ppppp-bbbbb')
  
  INSERT INTO #Test
  VALUES ('zzzz-xxxxx')
  
  --using PARSENAME
  SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
  FROM #Test
  
  --using LEFT and CHARINDEX
  SELECT LEFT(SomeField,CHARINDEX('-',SomeField) - 1)
  FROM #Test
  
  --using LEFT and PATINDEX
  SELECT LEFT(SomeField,PATINDEX('%-%',SomeField) - 1)
  FROM #Test
  
  
  --using CASE SUBSTRING and LEFT
  SELECT CASE SUBSTRING(SomeField,4,1)
  WHEN '-' THEN LEFT(SomeField,3)
  ELSE LEFT(SomeField,4)
  END
  FROM #Test
  
  
  --clean up
  DROP TABLE #Test

  
  

• fredkarm

  It's been around since 6.x sometime I believe.

  Very handy for some creative usage of period-delimited strings (like sorting ip-addresses)

  =;o)
  /Kenneth

  
  

• Big Ticket

  I try this

  select * from(

  select '0.0.18.1' as IpAddress union all

  select '0.1.1.2' union all

  select '0.0.2.1' union all

  select '0.0.0.1' ) z

  order by parsename(ipaddress,1),

  parsename(ipaddress,2),

  parsename(ipaddress,3),

  parsename(ipaddress,4);

  The result is

  0.0.0.1
  0.0.18.1
  0.0.2.1
  0.1.1.2

  But I would expect the following instead.

  0.0.0.1
  0.0.2.1
  0.0.18.1
  0.1.1.2

  Do I miss anything

  
  
• How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order