search_ip

On this page

Searches text for an IP address in a specified range.

Syntax

search_ip(<string>, cidr => <cidr>)

Arguments

ArgumentTypeDescription
<string>stringText containing IPv4 or IPv6 addresses.
cidrstringConstant. IPv4 or IPv6 CIDR range, or a single IP address. Must be valid and non-NULL.

Returns

Returns boolean: TRUE if a matching address is found, otherwise FALSE. Returns NULL if the input is NULL.

Examples

Find requests from the network 203.0.113.0/24 when the client address is embedded in a message:

VALUES
    ('req-17', 'client=203.0.113.7 checkout failed'),
    ('req-42', 'client=198.51.100.12 checkout failed'),
    ('req-63', 'client=203.0.113.91 checkout completed')
SELECT $0 AS request_id, $1 AS message
WHERE search_ip(message, cidr => '203.0.113.0/24')
SELECT request_id, message
ORDER BY request_id
+------------+----------------------------------------+
| request_id | message                                |
+------------+----------------------------------------+
| req-17     | client=203.0.113.7 checkout failed     |
| req-63     | client=203.0.113.91 checkout completed |
+------------+----------------------------------------+