![]() In this tutorial, you have learned how to use the PostgreSQL LIKE and ILIKE operators to query data using pattern matching.If you’re working with a database, whether large or small, there might be occasions when you need to search for some entries containing strings. PostgreSQL also provides some operators that act like the LIKE, NOT LIKE, ILIKE and NOT ILIKE operator as shown below: Operator If you use the LIKE operator instead, the query will not return any row. The BAR% pattern matches any string that begins with BAR, Bar, BaR, etc. ![]() For example: SELECTįirst_name ILIKE 'BAR%' Code language: SQL (Structured Query Language) ( sql ) In addition, the ILIKE operator matches value case-insensitively. PostgreSQL supports the ILIKE operator that works like the LIKE operator. The following query uses the NOT LIKE operator to find customers whose first names do not begin with Jen: SELECTįirst_name Code language: SQL (Structured Query Language) ( sql ) PostgreSQL extensions of LIKE operator The returned first names are C heryl, S herri, S herry, and T herasa. And is ended with any number of characters. ![]() And is followed by the literal string her.The pattern _her% matches any string that: You can combine the percent ( %) with underscore ( _) to construct a pattern as the following example: SELECT SELECTįirst_name Code language: SQL (Structured Query Language) ( sql ) It’s possible to use wildcards at the beginning and/or end of the pattern.įor example, the following query returns customers whose first name contains er string like Jenifer, Kimberly, etc. The fourth expression returns false because the pattern b_ matches any string that begins with the letter b and followed by any single character.The third expression returns true because the pattern ( _o_) matches any string that begins with any single character, followed by the letter o and ended with any single character.The second expression returns true because it matches any string that begins with the letter f and followed by any number of characters.The first expression returns true because the foopattern does not contain any wildcard character so the LIKE operator acts like the equal ( =) operator.See the following example: SELECT 'foo' LIKE 'foo', - true 'foo' LIKE 'f%', - true 'foo' LIKE '_o_', - true 'bar' LIKE 'b_' - false Code language: SQL (Structured Query Language) ( sql ) Let’s take some examples of using the LIKE operator Simple PostgreSQL LIKE examples PostgreSQL LIKE operator – pattern matching examples If the pattern does not contain any wildcard character, the LIKE operator behaves like the equal ( =) operator. ![]() The NOT LIKE operator returns true when the value does not match the pattern. To negate the LIKE operator, you use the NOT operator as follows: value NOT LIKE pattern Code language: SQL (Structured Query Language) ( sql ) The expression returns true if the value matches the pattern. The syntax of PostgreSQL LIKE operator is as follows: value LIKE pattern Code language: SQL (Structured Query Language) ( sql )
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |