Mastering Text-Based Queries in PostgreSQL

Mastering Text-Based Queries in PostgreSQL | CodeSignal Learn

Introduction and Overview

Welcome to the first lesson of our course on Advanced Query Techniques and Conditional Logic in PostgreSQL. In this lesson, we will focus on mastering text-based queries. By the end of this lesson, you will know how to search for patterns in text and handle case sensitivity effectively using PostgreSQL.

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Suppose we want to find characters with names that begin with "A". The query is: SQL SELECT DISTINCT character_name FROM characters WHERE character_name LIKE 'A%'; SELECT DISTINCT character_name FROM characters WHERE character_name LIKE 'A%'; The output is: text character_name ------------------------------- Ayo Aunt May Alexei Shostakov/Red Guardian Aldrich Killian America Chavez Agent Coulson Adrian Toomes/Vulture Alexander Pierce (8 rows) character_name ------------------------------- Ayo Aunt May Alexei Shostakov/Red Guardian Aldrich Killian America Chavez Agent Coulson Adrian Toomes/Vulture Alexander Pierce (8 rows) Here, any character name starting with "A" (case-sensitive) followed by any sequence of characters is selected.

The query to find character names that end with "a" (case-sensitive) preceded by any sequence of characters is: SQL SELECT DISTINCT character_name FROM characters WHERE character_name LIKE '%a'; SELECT DISTINCT character_name FROM characters WHERE character_name LIKE '%a'; The output is: text character_name ------------------------------ Ramonda Yelena Belova Hela Nakia Raza Clea Steve Rogers/Captain America Nebula Thena Gamora Frigga Minn-Erva Yondu Udonta (13 rows) character_name ------------------------------ Ramonda Yelena Belova Hela Nakia Raza Clea Steve Rogers/Captain America Nebula Thena Gamora Frigga Minn-Erva Yondu Udonta (13 rows) Any character name ending with "a" will be included, regardless of the preceding sequence of characters.

In PostgreSQL, you can use the = operator to find exact matches in text-based queries. This operator ensures only entries that exactly match the specified text are returned. To find exact matches for the character name "Tony Stark", you can use the following query: SQL SELECT character_name FROM characters WHERE character_name = 'Tony Stark'; SELECT character_name FROM characters WHERE character_name = 'Tony Stark'; The output is: text character_name ---------------- Tony Stark (1 row) character_name ---------------- Tony Stark (1 row) This query returns only the entries where the character_name is exactly "Tony Stark", with no additional characters or variations in case.

Placing % at the beginning and end of a text finds all entries that contain that substring. The % at the beginning means any sequence of characters (including an empty sequence) can come before the substring, and the % at the ends means any sequence of characters (including an empty sequence) can come after the substring. Let's take a look at an example: SQL SELECT DISTINCT character_name FROM characters WHERE character_name LIKE '%Tony Stark%'; SELECT DISTINCT character_name FROM characters WHERE character_name LIKE '%Tony Stark%'; The output of this query is: text character_name --------------------- Tony Stark Tony Stark/Iron Man (2 rows) character_name --------------------- Tony Stark Tony Stark/Iron Man (2 rows) The output indicates that there are two distinct entries in the character_name column that contain "Tony Stark": "Tony Stark" and "Tony Stark/Iron Man". "Tony Stark/Iron Man" did not appear in the query containing WHERE character_name = 'Tony Stark' because "Tony Stark/Iron Man" is not an exact match.

PostgreSQL provides two pattern-matching functions: LIKE and ILIKE . LIKE performs case-sensitive pattern matching, while ILIKE performs case-insensitive pattern matching. Let's revisit the example of finding the characters whose names begin with "A". In the characters table, the first letter of each character's name is capitalized. SQL SELECT character_name FROM characters WHERE character_name LIKE 'a%'; SELECT character_name FROM characters WHERE character_name LIKE 'a%'; The above query does not return any entries because no character names start with lowercase "a". The output is: text character_name ---------------- (0 rows) character_name ---------------- (0 rows) We can use ILIKE to find all names that begin with the letter "A"/"a" regardless of case. SQL SELECT DISTINCT character_name FROM characters WHERE character_name ILIKE 'a%'; SELECT DISTINCT character_name FROM characters WHERE character_name ILIKE 'a%'; The output of this query is: text character_name ------------------------------- Ayo Aunt May Alexei Shostakov/Red Guardian Aldrich Killian America Chavez Agent Coulson Adrian Toomes/Vulture Alexander Pierce (8 rows) character_name ------------------------------- Ayo Aunt May Alexei Shostakov/Red Guardian Aldrich Killian America Chavez Agent Coulson Adrian Toomes/Vulture Alexander Pierce (8 rows) The output shows how ILIKE can be used to query text without regard to case.

Sometimes, you might want to handle case sensitivity explicitly. We can do this using the UPPER and LOWER functions. These functions return their input in all capital letters and all lowercase letters, respectively. To find names that are fully uppercase, we use the UPPER function as follows: SQL SELECT character_name FROM characters WHERE character_name = UPPER(character_name); SELECT character_name FROM characters WHERE character_name = UPPER(character_name); UPPER(character_name) returns the character_name in all caps. Using WHERE character_name = UPPER(character_name);, we find all character names that remain unchanged when passed into the UPPER function. The output is: text character_name ---------------- JARVIS MJ MJ (3 rows) character_name ---------------- JARVIS MJ MJ (3 rows) The output shows the names of characters that only contain capital letters. Similarly, to find names in lowercase, we use the LOWER function. SQL SELECT actor FROM characters WHERE actor = LOWER(actor); SELECT actor FROM characters WHERE actor = LOWER(actor); The output is: text actor ------- (0 rows) actor ------- (0 rows) This query returns an empty table, as no actor names are in all lowercase.