Re: sort
From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 03/08/04
- Next message: Uri Dimant: "Re: How to check for duplicates"
- Previous message: Lasse: "How to check for duplicates"
- In reply to: Steve Kass: "Re: sort"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 8 Mar 2004 07:52:34 +0100
Nice!
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Steve Kass" <skass@drew.edu> wrote in message
news:OQqqAFKBEHA.1604@TK2MSFTNGP11.phx.gbl...
> Cute! You can also order by a function returning the ip as a bigint:
>
> create function ipVal (
> @ipAdd varchar(15)
> ) returns bigint as begin
> declare @a varchar(3), @b varchar(3), @c varchar(3), @d varchar(3)
> set @ipAdd = replace(@ipAdd,'.',space(1))
> exec master..xp_sscanf @ipAdd, '%s%s%s%s', @a output, @b output, @c
> output, @d output
> return 256.*(256.*(256.*@a + @b)+ @c) + @d
> end
>
> SK
>
> Itzik Ben-Gan wrote:
>
> >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)
> >
> >;-)
> >
> >
>
- Next message: Uri Dimant: "Re: How to check for duplicates"
- Previous message: Lasse: "How to check for duplicates"
- In reply to: Steve Kass: "Re: sort"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
