SQL中与IP相关的常见问题
来源：互联网 发布：墨西哥墙 知乎 编辑：程序博客网 时间：2024/05/23 20:38
1:得到客户端的IP地址 drop table #tb 3: 将IP地址段转成每三位用点号分开 （所影响的行数为 1 行）/************* IP **************/
declare @IP varchar(20),@hst varchar(20),@SQL varchar(100)
declare @str varchar(100)
set @str='PING '+Host_Name()
create table #tmp(aa varchar(200))
insert #tmp exec master..xp_cmdshell @str
select top 1 @IP = replace(left(aa,charindex(':',aa)-1),'Reply from ','')
from #tmp where aa like 'reply from %:%'
drop table #tmp
select @IP
2:得到网卡的物理地址create table #tb(re varchar(255))
insert into #tb exec master..xp_cmdshell 'IPconfig /all'select 网卡物理地址=substring(re,charindex(':',re)+1,255) from #tb where re like '%Physical Address. . . . . . . . . :%' create function getIP(@a varchar(15))
returns varchar(15)
As
begin
declare @s varchar(15)
set @s = ''
while charindex('.',@a) >0
begin
set @s = @s + right('000' + left(@a,charindex('.',@a)),4)
set @a = right(@a,len(@a)-charindex('.',@a))
end
set @s = @s + right('000' + @a,3)
return @s
end/*
Select dbo.getIP('202.1.110.2')
---------------
202.001.110.002*/
--drop function getIP<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
