{"id":1201,"date":"2024-12-23T09:44:18","date_gmt":"2024-12-23T02:44:18","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=1201"},"modified":"2025-01-03T14:22:39","modified_gmt":"2025-01-03T07:22:39","slug":"postgresql-similar-to","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-similar-to\/","title":{"rendered":"PostgreSQL SIMILAR TO Operator"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL SIMILAR TO<\/code> operator to search for a specific pattern in a column that matches a regular expression.<\/p>\n\n\n\n

Please note that you need a basic understanding of regular expressions<\/a> to continue this tutorial.<\/p>\n\n\n\n

Overview of the PostgreSQL SIMILAR TO Operator #<\/a><\/h2>\n\n\n\n

In PostgreSQL, the SIMILAR TO<\/code> operator returns true if the regular expression matches the whole string.<\/p>\n\n\n\n

Here’s the syntax of the SIMILAR TO<\/code> operator:<\/p>\n\n\n

SELECT<\/span>\n  column1,\n  column2\nFROM<\/span>\n  table_name<\/span>\nWHERE<\/span>\n  column1 SIMILAR<\/span> TO<\/span> pattern;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the SIMILAR TO<\/code> operator returns true only if the value in the pattern matches the entire value in column1<\/code>, or false otherwise. <\/p>\n\n\n\n
It differs from the expected behavior of matching a string with a regular expression, which can match any part of a string.<\/p>\n\n\n\n
Besides metacharacters used in a regular expression, you can use the _<\/code> and %<\/code> wildcard characters to denote a single character and zero or more characters, respectively. <\/p>\n\n\n\n
Typically, the dot (.<\/code>) character is a metacharacter that matches any single character except the newline, but the SIMILAR TO<\/code> operator does not treat it as a metacharacter.<\/p>\n\n\n\n
Example of PostgreSQL SIMILAR TO Operator #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the SIMILAR TO<\/code> operator with the products<\/code> table:<\/p>\n\n\n\n
\"PostgreSQL<\/figure>\n\n\n\n
The following SELECT<\/code> statement uses the SIMILAR TO<\/code> operator to select products with names that match a regular expression:<\/p>\n\n\n
SELECT<\/span>\n  product_name\nFROM<\/span>\n  products\nWHERE<\/span>\n  product_name SIMILAR<\/span> TO<\/span> '\\w+\\s*(Galaxy|iPhone)\\s*\\w+'<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
    product_name\n--------------------<\/span>\n Samsung Galaxy S24\n Apple iPhone 15<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s break down the regular expression '\\w+\\s*(Galaxy|iPhone)\\s*\\w+'<\/code>:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n