SQL statements that convert integers into IP strings in query statements

This article describes how to convert an integer into an IP string in a SQL query statement. If you need it, refer to explain.

This article describes how to convert an integer into an IP string in a SQL query statement. If you need it, refer to explain.

The data table structure is --

The Code is as follows:
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:
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 itr, * FROM ac_mainctls_new

The query result is --

Ipstr ctlip

The Code is as follows:
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.