Re: sort

From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 03/07/04 

Date: Sun, 7 Mar 2004 23:59:26 +0100

Or...

CREATE VIEW Patterns
AS
SELECT REPLICATE('_',N1.n) + '.'
  + REPLICATE('_',N2.n) + '.'
  + REPLICATE('_',N3.n) + '.'
  + REPLICATE('_',N4.n) AS pattern,
  1 AS s1, N1.n AS l1,
  N1.n+2 AS s2, N2.n AS l2,
  N1.n+N2.n+3 AS s3, N3.n AS l3,
  N1.n+N2.n+N3.n+4 AS s4, N4.n AS l4
FROM Nums AS N1, Nums AS N2, Nums AS N3, Nums AS N4
WHERE N1.n <= 3 AND N2.n <= 3 AND N3.n <= 3 AND N4.n <= 3
GO

SELECT T.*
FROM T JOIN Patterns
  ON ipAdd LIKE pattern
ORDER BY
  CAST(SUBSTRING(ipAdd,s1,l1) AS TINYINT),
  CAST(SUBSTRING(ipAdd,s2,l2) AS TINYINT),
  CAST(SUBSTRING(ipAdd,s3,l3) AS TINYINT),
  CAST(SUBSTRING(ipAdd,s4,l4) AS TINYINT)

;-) 

-- 
BG, SQL Server MVP
www.SolidQualityLearning.com
"Steve Kass" <skass@drew.edu> wrote in message
news:uCiIeA%23AEHA.1604@TK2MSFTNGP11.phx.gbl...
> Here's one solution:
>
> -- generate table with sample data
> create table T (
>   ipAdd varchar(15)
> )
> insert into T
> select
>      rtrim(abs(binary_checksum(newid()))%256) + '.'
>    + rtrim(abs(binary_checksum(newid()))%256) + '.'
>    + rtrim(abs(binary_checksum(newid()))%256) + '.'
>    + rtrim(abs(binary_checksum(newid()))%256)
> from Northwind..Orders
> go
>
> -- sort by ipAdd
> select ipAdd
> from T
> order by
>   0 + parsename(ipAdd,4),
>   0 + parsename(ipAdd,3),
>   0 + parsename(ipAdd,2),
>   0 + parsename(ipAdd,1)
> go
>
> drop table T
>
> SK
>
> hmc wrote:
>
> >What if I have a list of IP addresses that's in varchar?
> >
> >
>

Relevant Pages

  • Re: sort
    ... You can also order by a function returning the ip as a bigint: ... >>CREATE VIEW Patterns ... >> CASTAS TINYINT), ...
    (microsoft.public.sqlserver.programming)
  • Re: sort
    ... Cute! ... You can also order by a function returning the ip as a bigint: ... >CREATE VIEW Patterns ... > CASTAS TINYINT), ...
    (microsoft.public.sqlserver.programming)