IP address converted to digital, CHARINDEX, SUBSTRING

SET NOCOUNT on; Declare @I_PCity table (Ipstart nvarchar (255), area nvarchar (255), Cityidint, IPIDint) declare @IPStart nvarchar (255), @dianaint,   --Storage Point @dianbint, @diancint, @liea bigint,--Storage column @lieb bigint, @liec bigint, @lied bigint, @intlon bigint--stored integer INSERT into @I_PCity (Ipstart, area, Cityid, IPID)SelectIpstart, area, Cityid, IPID fromI_ipcity (NOLOCK)whereipid>=430000and Ipstart isNotNULLand IP3 is NULL-- while(Exists (SelectTop1* from@I_PCity)) BeginSet@IPStart = (SelectTop1Ipstart from@I_PCity); Set@diana = CHARINDEX ('.', @IPStart)--Subscript of the first pointSet@dianb = CHARINDEX ('.', @IPStart, @diana +1)  --Subscript of the second pointSet@dianc = CHARINDEX ('.', @IPStart, @dianb +1)  --Subscript for a third pointSet@liea = SUBSTRING (@IPStart,1, @diana-1)    --the value of the first columnSet@lieb =substring (@IPStart, @diana +1, @[email protected]1)--the value of the second columnSet@liec =substring (@IPStart, @dianb +1, @[email protected]1)--the value of the third columnSet@lied =substring (@IPStart, @dianc +1, LEN (@IPStart))--the value of the fourth columnSet@intlon = @lied + (@liec * +) + (@lieb *1000000) + (@liea *1000000000)  --spell integer update i_ipcitySetIP3 [email protected]whereIpid= (SelectTop1IPID from@I_PCity) Delete Top (1) from@I_PCity End

Return to the results found at the front desk:

Use [91hurong]go/** * * * object:storedprocedure [dbo].    [Proip] Script date:08/13/2015 08:40:16 * * * * **/SET ansi_nulls ongoset quoted_identifier ongoalter PROCEDURE [dbo]. [Proip]--Add The parameters forThe stored procedure here @str varchar ( -) Asbegin declare @IPStart nvarchar (255), --Store the incoming data @dianaint,   --Storage Point @dianbint, @diancint, @liea bigint,--Storage column @lieb bigint, @liec bigint, @lied bigint, @intlon bigint--integer stored asSet@IPStart = @str; --Incoming IP'1.11.6.6'--Set@IPStart ='1.11.6.6'; Set@diana = CHARINDEX ('.', @IPStart)--Subscript of the first pointSet@dianb = CHARINDEX ('.', @IPStart, @diana +1)  --Subscript of the second pointSet@dianc = CHARINDEX ('.', @IPStart, @dianb +1)  --Subscript for a third pointSet@liea = SUBSTRING (@IPStart,1, @diana-1)    --the value of the first columnSet@lieb =substring (@IPStart, @diana +1, @[email protected]1)--the value of the second columnSet@liec =substring (@IPStart, @dianb +1, @[email protected]1)--the value of the third columnSet@lied =substring (@IPStart, @dianc +1, LEN (@IPStart))--the value of the fourth columnSet@intlon = @lied + (@liec * +) + (@lieb *1000000) + (@liea *1000000000)  --spell integer DECLARE @inta bigintSet@inta = (SelectTop1IP3 fromI_ipcitywhereIp3> @intlon and Ipstart isNotNULLand IPID isNotNULLand IP3 isNotNULLORDER BY IP3 ASC)SelectTop1* fromI_ipcitywhereip3< @inta and Ipstart isNotNULLand IPID isNotNULLand IP3 isNotNULLORDER BY IP3 Desc--Return' 61.177.117.6 ' corresponds to the tableof data
END
EXEC Proip'61.177.117.6'

Description

 'Arg.ea'corresponding Location:1 2 3 4 5 6charindex ('.','Arg.ea') >0--a value greater than 0 indicates that the string area contains a string cityname; This example is true charindex ('.','Arg.ea',2)-From the second position, the letter'R'after starting to find, first determine whether ' g ' is '. ', no; continue to judge '. ' Is '. ', this expression is true SUBSTRING ('Arg.ea',1,2)--intercept string'Arg.ea'The first position in the second position, i.e. ' Ar '

IP address converted to digital, CHARINDEX, SUBSTRING