Ordenar campo texto como numérico.

SQL Server

02/05/2005

Tenho uma tabela que armazena endereços IP e preciso ordená-las pelos mesmos. Entretanto isso causa um grande problema, pois os valores ficam ordenados à partir do código alfabético do caracter e não do valore real, por exemplo:

172.16.0.10 <<======
172.16.0.100
172.16.0.101
172.16.0.102
172.16.0.11 <<======
172.16.0.110
172.16.0.111

Há alguma maneira de efetuar esta ordem de maneira coerente ? Tentei utilizar ORDER BY CAST(IP AS Numeric) mas não funciona devido aos pontos. Tentei também desmontar o IP utilizando SUBSTRING e CHARINDEX mas ficou muito complicado por causa tmb dos pontos.


Rc.salvador

Rc.salvador

Curtidas 0

Respostas

Gandalf.nho

Gandalf.nho

02/05/2005

Enquanto permanecer como texto ele irá ordenar alfabeticamente e não numericamente, a menos que todos os IPs tenham o mesmo tamanho e os pontos nos mesmos lugares (o que não dá)


GOSTEI 0
Tinorj

Tinorj

02/05/2005

Uma solução seria armazenar cada numero separado e fazer um campo calculado exibindo o IP já concatenado com os pontos, mas não sei qual seria esse custo para sua situação.

Abaixo segue um exemplo.

use Northwind

GO

if exists (select * from dbo.sysobjects where id = object_id(N´[dbo].[IPs]´) and OBJECTPROPERTY(id, N´IsUserTable´) = 1)
drop table [dbo].[IPs]
GO

CREATE TABLE [dbo].[IPs] (
[PARTE1] [int] NULL ,
[PARTE2] [int] NULL ,
[PARTE3] [int] NULL ,
[PARTE4] [int] NULL ,
[IP] AS (CONVERT(varchar(3),[PARTE1]) + ´.´ + CONVERT(varchar(3),[PARTE2]) + ´.´ + CONVERT(varchar(3),[PARTE3]) + ´.´ + CONVERT(varchar(3),[PARTE4]))
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[IPs] ADD 
CONSTRAINT [DF_IPs_PARTE1] DEFAULT (0) FOR [PARTE1],
CONSTRAINT [DF_IPs_PARTE2] DEFAULT (0) FOR [PARTE2],
CONSTRAINT [DF_IPs_PARTE3] DEFAULT (0) FOR [PARTE3],
CONSTRAINT [DF_IPs_PARTE4] DEFAULT (0) FOR [PARTE4],
CONSTRAINT [CK_IP_PARTE1] CHECK ([PARTE1] >= 0 and [PARTE1] < 255),
CONSTRAINT [CK_IP_PARTE2] CHECK ([PARTE2] >= 0 and [PARTE2] < 255),
CONSTRAINT [CK_IP_PARTE3] CHECK ([PARTE3] >= 0 and [PARTE3] < 255),
CONSTRAINT [CK_IP_PARTE4] CHECK ([PARTE4] >= 0 and [PARTE4] < 255)
GO


GO

INSERT INTO IPs VALUES (172,16,0,10)
INSERT INTO IPs VALUES (172,16,0,100)
INSERT INTO IPs VALUES (172,16,0,101)
INSERT INTO IPs VALUES (172,16,0,102)
INSERT INTO IPs VALUES (172,16,0,11)
INSERT INTO IPs VALUES (172,16,0,110)
INSERT INTO IPs VALUES (172,16,0,111)

GO

SELECT IP FROM IPs
ORDER BY PARTE1, PARTE2, PARTE3, PARTE4



GOSTEI 0
Psergio.p

Psergio.p

02/05/2005

Minha sugestão é a seguinte:

Criar uma UDF para retornar as partes do IP, veja abaixo o exemplo:

CREATE FUNCTION UDF_GETIP(@ENDERECO_IP VARCHAR(15),@PARTE INT)
RETURNS INT
AS
BEGIN
DECLARE @PARTE01 INT
DECLARE @PARTE02 INT
DECLARE @PARTE03 INT
DECLARE @PARTE04 INT
DECLARE @POSICAO INT

SET @POSICAO = CHARINDEX(´.´,@ENDERECO_IP)
SET @PARTE01 = SUBSTRING(@ENDERECO_IP,1,@POSICAO - 1)
SET @ENDERECO_IP = SUBSTRING(@ENDERECO_IP,@POSICAO + 1, LEN(@ENDERECO_IP))

SET @POSICAO = CHARINDEX(´.´,@ENDERECO_IP)
SET @PARTE02 = SUBSTRING(@ENDERECO_IP,1,@POSICAO - 1)
SET @ENDERECO_IP = SUBSTRING(@ENDERECO_IP,@POSICAO + 1, LEN(@ENDERECO_IP))

SET @POSICAO = CHARINDEX(´.´,@ENDERECO_IP)
SET @PARTE03 = SUBSTRING(@ENDERECO_IP,1,@POSICAO - 1)
SET @ENDERECO_IP = SUBSTRING(@ENDERECO_IP,@POSICAO + 1, LEN(@ENDERECO_IP))

SET @PARTE04 = SUBSTRING(@ENDERECO_IP,1,@POSICAO - 1)

IF @PARTE = 1 BEGIN
RETURN(@PARTE01)
END
IF @PARTE = 2 BEGIN
RETURN(@PARTE02)
END
IF @PARTE = 3 BEGIN
RETURN(@PARTE03)
END
IF @PARTE = 4 BEGIN
RETURN(@PARTE04)
END
RETURN 0
END



Depois veja como usar a função e order os valores:

SELECT IP FROM(
SELECT *,DBO.UDF_GETIP(IP,1) AS IP1,DBO.UDF_GETIP(IP,2) AS IP2,DBO.UDF_GETIP(IP,3) AS IP3,
DBO.UDF_GETIP(IP,4) AS IP4 FROM EnderecoIP) AS SQ
ORDER BY IP1,IP2,IP3,IP4



Espero que ajude, me dá um feedback depois!

[]
Paulo


GOSTEI 0
POSTAR