Database Engineering
  • Need of Database
  • Database and Database Management System
  • What is Relational Database Model ?
  • Installing Postgresql
    • Installing PostgreSQL on Linux
    • Installing PostgreSQL on Windows
    • Installing PostgreSQL on Mac
    • Postgres in Docker
  • What happens during the initial installation ?
    • Roles, Users & Groups
    • More examples on Roles
  • Sample Databases
    • DVD Rental Database
  • Querying Data
    • SELECT Query
    • Column Aliases
    • Order By
    • SELECT DISTINCT
    • DB-TASK-001
  • Filtering Data
    • WHERE ?
    • AND Operator
    • OR Operator
    • LIMIT
    • FETCH
    • IN
    • BETWEEN
    • LIKE
    • SIMILAR TO
    • IS NULL
    • ESCAPE
    • DB-TASK-002
  • Entity Relationship Diagram
    • What is an ER Diagram ?
    • Entity
    • Identifiers
    • Relationship
    • Attributes
    • Cardinality
    • Entity Relationship Diagram Tutorial
    • DB-TASK-003
  • Joins
    • Joins
    • Joins With Employee and Departments
  • Joins on E-Commerce
Powered by GitBook
On this page
  • How SIMILAR TO Works
  • Basic Syntax and Examples
  • 1) Basic SIMILAR TO Example
  • 2) Using SIMILAR TO with Multiple Patterns
  • 3) SIMILAR TO with Wildcards
  1. Filtering Data

SIMILAR TO

In SQL, when you need to perform pattern matching with more advanced capabilities than the LIKE operator, you can use the SIMILAR TO operator. The SIMILAR TO operator in PostgreSQL offers a way to search for patterns that follow regular expression rules.

Imagine you want to find customers in a database whose names contain a combination of specific characters.

For example, you might remember that the customer names contain specific combinations of letters, but you don’t know the exact order. Instead of searching with multiple LIKE statements or using wildcard characters excessively, you can use the SIMILAR TO operator, which can provide much more powerful and flexible pattern matching.

How SIMILAR TO Works

The SIMILAR TO operator allows you to match a pattern to a string using regular expression syntax, similar to POSIX regular expressions. The syntax for SIMILAR TO is

value SIMILAR TO pattern

Where:

  • value is the string or column to be matched.

  • pattern is the regular expression pattern that you want to compare against.

The SIMILAR TO operator is used to check if the value matches the given regular expression pattern. If it does, the operator returns TRUE; otherwise, it returns FALSE.

Basic Syntax and Examples

Let’s look at some basic examples of using the SIMILAR TO operator in PostgreSQL.

1) Basic SIMILAR TO Example

Let’s say you want to find customers whose first names start with either "Jen", "Jon", or "Jan". Instead of writing multiple LIKE queries, you can use SIMILAR TO with a single pattern.

SELECT first_name, last_name
FROM customer
WHERE first_name SIMILAR TO 'J(e|o|a)n%';

In this example:

  • The pattern J(e|o|a)n% matches any string that starts with "J" followed by either "e", "o", or "a", and then any sequence of characters (%).

  • The | character is used to specify alternatives within the regular expression.

2) Using SIMILAR TO with Multiple Patterns

If you want to match several different patterns, SIMILAR TO lets you combine them into one regular expression. For example, to find customer names that start with "A" or "B" and have two additional characters, you could use

SELECT first_name, last_name
FROM customer
WHERE first_name SIMILAR TO '[AB]__';

In this query:

  • [AB] means the first character must be "A" or "B".

  • __ means there should be exactly two characters following it.

3) SIMILAR TO with Wildcards

You can also use wildcards in the SIMILAR TO pattern. For instance, if you want to find any first name starting with "S" and followed by at least one character.

SELECT first_name, last_name
FROM customer
WHERE first_name SIMILAR TO 'S%';

Here, % is the wildcard matching any sequence of characters (just like in LIKE).

PreviousLIKENextIS NULL

Last updated 4 months ago