Omskrive Oracle funktion til MS SQL.

Se om du kan bruge følgende.
Jeg kan ikke bruge RETURN(@n-ipaddress), da RETURN på SQL Server kun kan retunere vanlig integer, og nummeret bliver for stort. Derfor returnerer jeg værdien som SELECT.

CREATE PROCEDURE IP_TO_NUMBER
    @p_ipaddress varchar(100) 
AS

DECLARE @c_ipaddress1 as char(3)
DECLARE @c_ipaddress2 as char(3)
DECLARE @c_ipaddress3 as char(3)
DECLARE @c_ipaddress4 as char(3)
DECLARE @c_ipaddress as varchar(15)
DECLARE @n_ipaddress as bigint
DECLARE @n_dot_pos as int
Declare @c_padds as char(3)

Set     @c_padds = '000'
Set    @c_ipaddress = @p_ipaddress
Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)

Set    @c_ipaddress1 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress1 = left(@c_padds, 3 -  len(@c_ipaddress1)) + @c_ipaddress1
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)
Set    @c_ipaddress2 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress2 = left(@c_padds, 3 -  len(@c_ipaddress2)) + @c_ipaddress2
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)
Set    @c_ipaddress3 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress3 = left(@c_padds, 3 -  len(@c_ipaddress3)) + @c_ipaddress3
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @c_ipaddress4 = left(@c_padds, 3 -  len(@c_ipaddress)) + @c_ipaddress
Set      @c_ipaddress = (@c_ipaddress1 + @c_ipaddress2 + @c_ipaddress3 + @c_ipaddress1)

Set     @n_ipaddress = cast(@c_ipaddress as bigint)

select @n_ipaddress as n_ipaddress

GO

Jeg fjernet lige en unødvendig linie:

CREATE PROCEDURE IP_TO_NUMBER
    @p_ipaddress varchar(100) 
AS

DECLARE @c_ipaddress1 as char(3)
DECLARE @c_ipaddress2 as char(3)
DECLARE @c_ipaddress3 as char(3)
DECLARE @c_ipaddress4 as char(3)
DECLARE @c_ipaddress as varchar(15)
DECLARE @n_ipaddress as bigint
DECLARE @n_dot_pos as int
Declare @c_padds as char(3)

Set     @c_padds = '000'
Set    @c_ipaddress = @p_ipaddress
Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)

Set    @c_ipaddress1 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress1 = left(@c_padds, 3 -  len(@c_ipaddress1)) + @c_ipaddress1
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)
Set    @c_ipaddress2 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress2 = left(@c_padds, 3 -  len(@c_ipaddress2)) + @c_ipaddress2
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)
Set    @c_ipaddress3 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress3 = left(@c_padds, 3 -  len(@c_ipaddress3)) + @c_ipaddress3
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @c_ipaddress4 = left(@c_padds, 3 -  len(@c_ipaddress)) + @c_ipaddress

Set     @n_ipaddress = cast((@c_ipaddress1 + @c_ipaddress2 + @c_ipaddress3 + @c_ipaddress1) as bigint)

select @n_ipaddress as n_ipaddress
GO

Sorry, det skal være en function og ikke procedure, jeg prøver lige at omskrive denne....

Her er den som function.

CREATE FUNCTION IP_TO_NUMBER     (@p_ipaddress varchar(15))
RETURNS bigint AS 

BEGIN
DECLARE @c_ipaddress1 char(3)
DECLARE @c_ipaddress2 char(3)
DECLARE @c_ipaddress3 char(3)
DECLARE @c_ipaddress4 char(3)
DECLARE @c_ipaddress varchar(15)
DECLARE @n_ipaddress bigint
DECLARE @n_dot_pos int
Declare @c_padds char(3)


Set     @c_padds = '000'
Set    @c_ipaddress = @p_ipaddress
Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)

Set    @c_ipaddress1 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress1 = left(@c_padds, 3 -  len(@c_ipaddress1)) + @c_ipaddress1
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)
Set    @c_ipaddress2 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress2 = left(@c_padds, 3 -  len(@c_ipaddress2)) + @c_ipaddress2
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @n_dot_pos = CHARINDEX('.',@c_ipaddress)
Set    @c_ipaddress3 = cast(SUBSTRING (@c_ipaddress,1,@n_dot_pos-1) as varchar(3))
Set    @c_ipaddress3 = left(@c_padds, 3 -  len(@c_ipaddress3)) + @c_ipaddress3
Set    @c_ipaddress = SUBSTRING(@c_ipaddress,@n_dot_pos+1,len(@c_ipaddress)-@n_dot_pos)

Set    @c_ipaddress4 = left(@c_padds, 3 -  len(@c_ipaddress)) + @c_ipaddress

Set     @n_ipaddress = cast((@c_ipaddress1 + @c_ipaddress2 + @c_ipaddress3 + @c_ipaddress1) as bigint)


Return(@n_ipaddress)
END

Når jeg selecter

"SELECT * FROM r WHERE s = 0 AND i = 498 Order by dbo.IP_TO_NUMBER(ip)"

får jeg følgende fejl ??

"Invalid length parameter passed to the substring function."

Hvad betyder det. ?

Stig

Jeg skal da huske at give dig dine point, det andet er jo bare et tillægs spørgsmål.

Jeg har lavet en simpel test selv, og kan umiddelbart godt lave en 'Order by dbo.IP_TO_NUMBER(ip)'.

Det kunne derfor tyde på at du har enten en tom ip-adresse, eller en ip adresse der ikke indeholder 3 punktummer. Funktionen tager ikke højde for at der bliver sendt en forkært ip-adresse i form af NULL-værdi, et tomt felt eller en ip-adresse som ikke inneholder 3 punktummer.

Kan du checke om du har nogle forkærte ip-adresser?
Fungerer funktionen ellers som den skal?