SQL statement that can obtain the IP address of the client

Use SQL statements to obtain the Client IP Address Code

Use SQL statements to obtain the Client IP Address Code

-- 1: Obtain the IP address of the client.
/************* 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_mongoshell @ 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: Obtain the physical address of the NIC.
Create table # tb (re varchar (255 ))
Insert into # tb exec master .. xp_mongoshell 'ipconfig/all'

Select Nic Physical Address = substring (re, charindex (':', re) + 1,255) from # tb where re like '% Physical Address .........: %'

Drop table # tb
Go


-- 3: convert an IP address segment into three segments separated by the DoT number
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 ('.', @ ))
End
Set @ s = @ s + right ('000' + @ a, 3)
Return @ s
End

/*
Select dbo. getIP ('192. 1.110.2 ')
---------------
202.001.110.002

(The number of affected rows is 1)
*/
-- Drop function getIP