(一)Oracle中:
(1) IP转为数字:
- create or replace function ip2number(ip varchar2)
- return number
- is
- ip_num_hex varchar2(80);
- begin
- if (regexp_like(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$')) then
- ip_num_hex := lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\1'), 'XX')),2,'0') ||
- lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\2'), 'XX')),2,'0') ||
- lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\3'), 'XX')),2,'0') ||
- lpad(trim(to_char(regexp_replace(ip, '^(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})$', '\4'), 'XX')),2,'0');
- return to_number(ip_num_hex, 'XXXXXXXX');
- else
- return -1;
- end if;
- exception
- when others then
- return -99999999999;
- end;
- select ip2number('169.254.55.6') from dual;
- IP2NUMBER('169.254.55.6')
- -------------------------
- 2852009734
(2) 数字转为IP:
- create or replace function number2ip(num number)
- return varchar2 is
- ip_num_hex varchar2(8);
- begin
- ip_num_hex := lpad(trim(to_char(num, 'XXXXXXXX')), 8, '0');
- return to_number(substr(ip_num_hex, 1, 2), 'XX') || '.' ||
- to_number(substr(ip_num_hex, 3, 2), 'XX') || '.' ||
- to_number(substr(ip_num_hex, 5, 2), 'XX') || '.' ||
- to_number(substr(ip_num_hex, 7, 2), 'XX');
- exception
- when others then
- dbms_output.put_line(sqlerrm);
- return null;
- end;
- select number2ip(2852009734) from dual;
- NUMBER2IP(2852009734)
- --------------------------------------------------------------------------------
- 169.254.55.6
(二)SQL Server中:
(1) IP转为数字:
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_IP2Int]') and xtype in (N'FN', N'IF', N'TF'))
- drop function [dbo].[f_IP2Int]
- GO
- /*--字符型 IP 地址转换成数字 IP
- /*--调用示例
- select dbo.f_IP2Int('192.168.0.11')
- select dbo.f_IP2Int('12.168.0.1')
- --*/
- CREATE FUNCTION f_IP2Int(
- @ip char(15)
- )RETURNS bigint
- AS
- BEGIN
- DECLARE @re bigint
- SET @re=0
- SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
- ,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
- FROM(
- SELECT ID=CAST(16777216 as bigint)
- UNION ALL SELECT 65536
- UNION ALL SELECT 256
- UNION ALL SELECT 1)A
- RETURN(@re)
- END
- GO
(2) 数字转为IP:
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_Int2IP]') and xtype in (N'FN', N'IF', N'TF'))
- drop function [dbo].[f_Int2IP]
- GO
- /*--数字 IP 转换成格式化 IP 地址
- /*--调用示例
- select dbo.f_Int2IP(3232235531)
- select dbo.f_Int2IP(212336641)
- --*/
- CREATE FUNCTION f_Int2IP(
- @IP bigint
- )RETURNS varchar(15)
- AS
- BEGIN
- DECLARE @re varchar(15)
- SET @re=''
- SELECT @re=@re+'.'+CAST(@IP/ID as varchar)
- ,@IP=@IP%ID
- from(
- SELECT ID=CAST(16777216 as bigint)
- UNION ALL SELECT 65536
- UNION ALL SELECT 256
- UNION ALL SELECT 1)a
- RETURN(STUFF(@re,1,1,''))
- END