1: Get the client's IP address / ************ 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 @strselect Top 1 @ IP = Replace (LEFT (AA, Charindex (', AA) -1),' Reply from ',') from #tmp where aa like 'reply from%:%' drop table #tmpselect @ip
2: Get the physical address of the NIC CREATE TABLE #TB (Re Varchar (255)) INSERT INTO #TB EXEC MASTER..XP_CMDSHELL 'ipconfig / all'
SELECT NIC Physics Address = Substring (Re, Charindex (':', RE) 1, 255) from #tb where re like '% physical address..................
Drop Table #TB
3: Turn the IP address segment into every three bits of creating create function getip (@a varchar (15)) Returns varchar (15) asbegindeclare @s varchar (15) set @s = '' While Charindex ('.' , @ a)> 0begin set @S = @S Right ('000' left (@ a, charindex (', @ a)), 4) set @a = Right (@ a, len (@A ) -Charindex ('.', @ a)) endset @S = @S Right ('000' @ a, 3) Return @send
/ * SELECT DBO.GETIP ('202.1.110.2') --------------- 202.001.110.002
(The number of rows affects is 1 line) * / - DROP FUNCTION GETIP
