1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
USE [ipv] GO /****** Object: Table [dbo].[ipv4_KR] Script Date: 2020-05-18 오후 5:12:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[ipv4_KR]( [IDX] [int] NOT NULL, [Standard_Date] [varchar](20) NOT NULL CONSTRAINT [DF_ipv4_KR_Standard_Date] DEFAULT (''), [Cuntry_Code] [varchar](10) NOT NULL CONSTRAINT [DF_ipv4_KR_Cuntry_Code] DEFAULT (''), [Start_IP_Number] [float] NOT NULL CONSTRAINT [DF_ipv4_KR_Start_IP_Number] DEFAULT ((0)), [End_IP_Number] [float] NOT NULL CONSTRAINT [DF_ipv4_KR_End_IP_Number] DEFAULT ((0)), [Start_IP] [varchar](20) NOT NULL CONSTRAINT [DF_ipv4_KR_Start_IP] DEFAULT (''), [End_IP] [varchar](20) NOT NULL CONSTRAINT [DF_ipv4_KR_End_IP] DEFAULT (''), [PREFIX] [varchar](20) NOT NULL CONSTRAINT [DF_ipv4_KR_PREFIX] DEFAULT (''), [Regi_Date] [varchar](20) NOT NULL CONSTRAINT [DF_ipv4_KR_Regi_Date] DEFAULT (''), CONSTRAINT [PK_ipv4_KR] PRIMARY KEY CLUSTERED ( [IDX] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
----------------------------------------------------------------------------------------------------------- -- Split 유형의 함수 -- 문자열에서 구분자(@iSeparator)로 몇번째 단어 가져오기 -- 예: SELECT 데이터베이스명.소유자명.fn_GetIdxDataLikeSplit('가-나-다',2,'-') --> '나' ----------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------- -- 프로시저 생성 ----------------------------------------------------------------------------------------------------------- Alter FUNCTION [dbo].[fn_GetIdxDataLikeSplit] ( @iText VARCHAR(200), @idx INT, @iSeparator VARCHAR(10) = '.' ) RETURNS VARCHAR(200) AS BEGIN DECLARE @wData VARCHAR(200) DECLARE @wText VARCHAR(200) DECLARE @wSeparator VARCHAR(10) DECLARE @wNum INT SET @wData = '' SET @wNum = 1; SET @wSeparator = LTRIM(RTRIM(@iSeparator)); SET @wText = LTRIM(RTRIM(@iText)) + @wSeparator; IF CHARINDEX(@wSeparator, @iText) > 0 BEGIN WHILE @idx >= @wNum BEGIN IF CHARINDEX(@wSeparator, @wText) > 0 BEGIN -- 문자열의 인덱스 위치의 요소를 반환 SET @wData = SUBSTRING(@wText, 1, CHARINDEX(@wSeparator, @wText) - 1); SET @wData = LTRIM(RTRIM(@wData)); -- 반환된 문자는 버린후 좌우공백 제거 SET @wText = LTRIM(RTRIM(RIGHT(@wText, LEN(@wText) - (LEN(@wData) + LEN(@iSeparator))))) END ELSE BEGIN SET @wData = '' END SET @wNum = @wNum + 1 END END ELSE BEGIN SET @wData = @iText END -- HEO ADd if Len(@wData) =1 begin Set @wData = '00' + @wData end else if(Len(@wData) =2) begin Set @wData = '0' + @wData end RETURN(@wData) END |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Declare @IDX int Declare @Start_IP varchar(20), @End_IP varchar(20) Declare @Start_IP_01 varchar(3),@Start_IP_02 varchar(3),@Start_IP_03 varchar(3),@Start_IP_04 varchar(3) Declare @End_IP_01 varchar(3),@End_IP_02 varchar(3),@End_IP_03 varchar(3),@End_IP_04 varchar(3) Declare con_cursor cursor for SELECT IDX, Start_IP, End_IP FROM [IBE_Basic_Code].[dbo].[ipv4_KR] open con_cursor --커스를 연다. fetch next from con_cursor into @IDX, @Start_IP, @End_IP while (@@fetch_status <>-1) begin if (@@fetch_status = -2) continue begin Set @Start_IP_01 = [dbo].[fn_GetIdxDataLikeSplit](@Start_IP,1,'.') Set @Start_IP_02 = [dbo].[fn_GetIdxDataLikeSplit](@Start_IP,2,'.') Set @Start_IP_03 = [dbo].[fn_GetIdxDataLikeSplit](@Start_IP,3,'.') Set @Start_IP_04 = [dbo].[fn_GetIdxDataLikeSplit](@Start_IP,4,'.') Set @End_IP_01 = [dbo].[fn_GetIdxDataLikeSplit](@End_IP,1,'.') Set @End_IP_02 = [dbo].[fn_GetIdxDataLikeSplit](@End_IP,2,'.') Set @End_IP_03 = [dbo].[fn_GetIdxDataLikeSplit](@End_IP,3,'.') Set @End_IP_04 = [dbo].[fn_GetIdxDataLikeSplit](@End_IP,4,'.') update [IBE_Basic_Code].[dbo].[ipv4_KR] set Start_IP_Number = CONVERT(numeric, @Start_IP_01 + @Start_IP_02 + @Start_IP_03 + @Start_IP_04) ,End_IP_Number = CONVERT(numeric, @End_IP_01 + @End_IP_02 + @End_IP_03 + @End_IP_04) where IDX = @IDX end fetch next from con_cursor into @IDX, @Start_IP, @End_IP end close con_cursor Deallocate con_cursor |