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)
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)
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)
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?
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.