SQL statements that convert integers into IP strings in query statements

The data table structure is --

The code is as follows:	Copy code
Create table [dbo]. [ac_mainctls_new] ( [Id] [int] NULL, [Ctlip] [bigint] NULL, [Ctlname] [char] (30) COLLATE Chinese_PRC_CI_AS NULL, )

Ctlip is the IP address of the device, which is stored as an integer.

Although an integer can be converted into an IP string by using a program, it is a little troublesome to add one operation. So I want to convert it into an IP string directly in the SQL query statement.

 

After thinking and debugging, I completed the query statement --

The code is as follows:	Copy code
Select cast (ctlip/0x1000000 AS varchar (3) + '. '+ CAST (ctlip/0 x 10000% 0x100 AS varchar (3) + '. '+ CAST (ctlip/0 x 100% 0x100 AS varchar (3) + '. '+ CAST (ctlip % 0x100 AS varchar (3) AS ipstr, * FROM ac_mainctls_new

Copy code

 

The query result is --

Ipstr ctlip

The code is as follows:	Copy code
192.168.10.32 3232238112 IP address 192.168.10.35 3232238115 IP address 192.168.10.21 3232238101 IP address 192.168.10.19 3232238099

 

Verification passed.