In this tutorial, we will explore how to use the LIKE operator in PostgreSQL to check whether a specific “contains” contain a specific or pattern-like substring.
It is good to keep in mind that the LIKE operator performs a case-insensitive pattern matching on the target string.
Sample Table
Let us start by defining a sample table for demonstration purposes. Feel free to skip this section if you have an existing table.
id SERIAL PRIMARY KEY,
device_name VARCHAR(50),
ip_address INET,
mac_address MACADDR,
connection_type VARCHAR(20)
);
INSERT INTO network_info (device_name, ip_address, mac_address, connection_type)
VALUES
('Device 1', '192.168.0.10', '00:11:22:33:44:55', 'Ethernet'),
('Device 2', '192.168.0.11', 'AA:BB:CC:DD:EE:FF', 'Wi-Fi'),
('Device 3', '192.168.0.12', '11:22:33:44:55:66', 'Ethernet'),
('Device 4', '192.168.0.13', 'AA:BB:CC:DD:EE:FF', 'Wi-Fi'),
('Device 5', '192.168.0.14', '00:11:22:33:44:55', 'Ethernet'),
('Device 6', '192.168.0.15', 'AA:BB:CC:DD:EE:FF', 'Wi-Fi'),
('Device 7', '192.168.0.16', '11:22:33:44:55:66', 'Ethernet'),
('Device 8', '192.168.0.17', 'AA:BB:CC:DD:EE:FF', 'Wi-Fi'),
('Device 9', '192.168.0.18', '00:11:22:33:44:55', 'Ethernet'),
('Device 10', '192.168.0.19', 'AA:BB:CC:DD:EE:FF', 'Wi-Fi');
In this case, we define a table that stores the network information with the columns as follows:
- The id is an auto-incrementing integer that serves as the primary key.
- The device_name stores the name or identifier of the network device.
- The ip_address stores the IP address of the device which is represented using the INET data type.
- The mac_address stores the MAC address of the device which is represented using the MACADDR data type.
- The connection_type stores the type of connection that the device uses such as Ethernet or Wi-Fi.
Resulting Table:
PostgreSQL LIKE Operator
Unfortunately, PostgreSQL does not have a “contains” function like SQL server or MySQL. However, we can use the LIKE operator to perform the pattern matching.
For example, suppose we want to retrieve all devices whose mac_address contains the :FF format. Then, we can use the LIKE operator with a wildcard % to match any character before or after the value.
FROM network_info
WHERE mac_address::text ILIKE '%FF';
This should return the table as follows:
NOTE: The LIKE operator doesn’t work directly with the MACADDR data type in PostgreSQL. Instead, we can use the ILIKE operator for case-insensitive pattern matching on a string representation of the MACADDR column.
Conclusion
We discussed the fundamentals of performing the pattern matching in PostgreSQL using the LIKE and ILIKE operators.
