Filtering text in SQL

Filtering text in SQL is essential for narrowing down query result based on specific criteria.

Common Types of Text Filtering in SQL:

1. Exact Match (=) : Filters rows where the column matches the text exactly.

• Example:

This query returns all person whose first name is exactly ‘Angelica’.

SELECT * FROM person WHERE  first_name = 'Angelica';

2. Partial Match (LIKE) : Filters rows where the column contains or starts with/ends with specific patterns.

• Example:

This query returns all person whose first name starts with ‘A’. The % is a wildcard that represents any sequence of characters.

SELECT * FROM person WHERE first_name LIKE 'A%';

3. Not match (NOT LIKE): is used to filter out rows where a column’s value does not match a specified pattern. It’s the inverse of the LIKE operator.

• Example:

This query returns all person whose first name does not start with ‘A’.

SELECT * FROM person WHERE first_name NOT LIKE 'Angelica';

4. Regular Expression Matching (REGEXP): Filters based on more complex patterns using regular expressions (specific to certain databases like MySQL or PostgreSQL).

• Example:

This query returns person whose names start with ‘An’ followed by ‘g’ and any number of characters.

SELECT * FROM person WHERE first_name REGEXP 'An[g]*';

5. Inclusion Match (IN): Filters rows where the column matches any of the values in a list.

This query returns person whose first names are either ‘Angelica’, ‘Sonia’, or ‘Paul’.

SELECT * FROM person WHERE first_name IN ('Angelica', 'Sonia', 'Paul');

Summary:

1. Exact Match (=): For precise text filtering.

2. Partial Match (LIKE): For pattern-based filtering with wildcards.

3. Not match (NOT LIKE) : Exclude rows that fit specific patterns.

And there are more ways to use NOT LIKE but that will be on another occasion 🤓.

1. Regular Expression Match (REGEXP): For complex pattern matching.

2. Inclusion Match (IN): To check for specific values in a list.

Each method serves different use cases depending on the level of precision or flexibility needed for text matching in SQL queries 👩‍💻.