IPTOINT Function

Computes an integer value for a four-octet internet protocol (IP) address. Source value must be a valid IP address or a column reference to IP addresses.

IP addresses must be in the following format:

aaa.bbb.ccc.ddd

where aaa, bbb, ccc, and ddd, are integers 0 - 255, inclusive.

Note

IPv6 addresses are not supported.

The formula used to compute the integer equivalent of the above IP address is the following:

(aaa * 256³) + (bbb * 256²) + (ccc * 256) + (ddd)

As a result, each valid IP address has a unique integer equivalent.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Numeric literal example:

iptoint('1.2.3.4' )

Output: Returns the integer value 16909060.

Column reference example:

iptoint(IpAddr)

Output: Returns the value of the IpAddr column converted to an integer value.

Syntax and Arguments

iptoint(column_ipaddr)

Argument	Required?	Data Type	Description
column_ipaddr	Y	string	Column name or string literal identifying the IP address to convert to an integer value

For more information on syntax standards, see Language Documentation Syntax Notes.

column_ipaddr

Name of the column or IP address literal whose values are used to compute the equivalent integer value.

Missing input values generate missing results.
Multiple columns and wildcards are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	String literal or column reference (IP address)	4.3.2.1

Examples

Astuce

For additional examples, see Common Tasks.

Example - Convert IP addresses to integers

This examples illustrates how you can convert IP addresses to numeric values for purposes of comparison and sorting.

Functions:

Item	Description
IPTOINT Function	Computes an integer value for a four-octet internet protocol (IP) address. Source value must be a valid IP address or a column reference to IP addresses.
IPFROMINT Function	Computes a four-octet internet protocol (IP) address from a 32-bit integer input.

Source:

Your dataset includes the following values for IP addresses:

IpAddr
192.0.0.1
10.10.10.10
1.2.3.4
1.2.3
http://12.13.14.15
https://16.17.18.19

Transformation:

When the above data is imported, the application initially types the column as URL values, due to the presence of the http:// and https:// protocol identifiers. Select the IP Address data type for the column. The last three values are listed as mismatched values. You can fix the issues with the last two entries by applying the following transform, which matches on both http:// and https:// strings:

Transformation Name	`Replace text or pattern`
Parameter: Column	IpAddr
Parameter: Find	`http%?://`
Parameter: Replace with	''

Note

The %?Wrangle matches zero or one time on any character, which enables the matching on both variants of the protocol identifier.

Now, only the 1.2.3 value is mismatched. Perhaps you know that there is a missing zero at the end of it. To add it back, you can do the following:

Transformation Name	`Replace text or pattern`
Parameter: Column	IpAddr
Parameter: Find	`1.2.3[end]`
Parameter: Replace with	'1.2.3.0'
Parameter: Match all occurrences	true

All values in the column should be valid for the IP Address data type. To convert these values to their integer equivalents:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	IPTOINT(IpAddr)
Parameter: New column name	'ip_as_int'

You can now manipulate the data based on this numeric key. To convert the integer values back to IP addresses for checking purposes, use the following:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	IPFROMINT(ip_as_int)
Parameter: New column name	'ip_check'

Results:

X	ip_as_int	ip_check
192.0.0.1	3221225473	192.0.0.1
10.10.10.10	168430090	10.10.10.10
1.2.3.4	16909060	1.2.3.4
1.2.3.0	16909056	1.2.3.0
12.13.14.15	202182159	12.13.14.15
16.17.18.19	269554195	16.17.18.19

Dans cette section: