Product Documentation

Appearance

SIMILAR TO ​

Define a condition where an entire character string matches (or does not match) a pattern of characters.

Syntax ​

string [NOT] SIMILAR TO pattern [ESCAPE 'character']
pattern

SIMILAR TO is akin to LIKE, in that the SIMILAR TO operator succeeds only if its pattern matches the entire string. A SIMILAR TO pattern may include POSIX regex notation, but the results are likely to differ from regex results where the pattern can match any part of the string. The pattern also supports the following wildcard characters:

  • % (matches any set of characters)
  • _ (matches any one character)

The pattern for SIMILAR TO must contain constants or query parameters. You cannot apply SIMILAR TO to function expressions or names of table columns.

ESCAPE 'character'

Identifies a single escape character that you can use when you want pattern matching to find actual percent and underscore characters, rather than use these characters as wildcards. The character must be identified in the ESCAPE clause, and that character must be placed before the percent or underscore character in the pattern itself.

For example:

premdb=# SELECT * FROM match 
WHERE ftscore SIMILAR TO '8-%';
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
      16 | 2008-05-11 00:00:00 |   27 |   74 | 8-1     | 2-0
      18 | 2010-05-09 00:00:00 |   14 |   95 | 8-0     | 2-0
      20 | 2011-08-28 00:00:00 |   26 |   51 | 8-2     | 3-1
      21 | 2012-12-23 00:00:00 |   14 |   52 | 8-0     | 3-0
       8 | 1999-09-19 00:00:00 |   28 |   85 | 8-0     | 4-0
(5 rows)

You can specify a regex-style array as part of a SIMILAR TO condition. For example, this query looks for ftscore values that end with 6, 7, 8, or 9:

premdb=# SELECT * FROM match 
WHERE SUBSTR(ftscore,3,1) SIMILAR TO '%(6|7|8|9)' 
ORDER BY seasonid,matchday;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
       3 | 1994-08-20 00:00:00 |   16 |   73 | 1-6     |    
       3 | 1995-04-01 00:00:00 |   36 |   79 | 1-7     |    
       4 | 1996-02-25 00:00:00 |    8 |   75 | 0-6     | 0-2
       6 | 1997-08-24 00:00:00 |    4 |   63 | 0-6     | 0-3
       6 | 1997-12-06 00:00:00 |   42 |   63 | 1-6     | 1-1
       6 | 1998-05-02 00:00:00 |   47 |   91 | 2-6     | 2-2
       7 | 1999-01-16 00:00:00 |   23 |   75 | 2-6     | 1-1
       7 | 1999-02-06 00:00:00 |   30 |   75 | 1-8     | 1-2
       7 | 1999-04-24 00:00:00 |   27 |   51 | 1-6     | 0-3
      10 | 2002-02-09 00:00:00 |   21 |   73 | 0-6     | 0-2
      11 | 2003-04-05 00:00:00 |   13 |   71 | 1-6     | 1-3
      11 | 2003-04-12 00:00:00 |   28 |   75 | 2-6     | 1-4
      11 | 2003-04-26 00:00:00 |   44 |   73 | 0-6     | 0-1
      16 | 2008-04-12 00:00:00 |   17 |   52 | 0-6     | 0-3
      16 | 2008-04-28 00:00:00 |   17 |   51 | 2-6     | 1-2
      18 | 2009-08-15 00:00:00 |   18 |   51 | 1-6     | 0-3
      18 | 2010-04-03 00:00:00 |   11 |   74 | 1-6     | 0-5
      19 | 2010-08-21 00:00:00 |   46 |   63 | 0-6     | 0-1
      20 | 2011-10-23 00:00:00 |   26 |   74 | 1-6     | 0-1
      20 | 2012-04-14 00:00:00 |   29 |   74 | 1-6     | 0-2
      21 | 2013-04-27 00:00:00 |   28 |   73 | 0-6     | 0-2
      22 | 2014-03-22 00:00:00 |   12 |   73 | 3-6     | 2-2
(22 rows)

You can run an equivalent query by using several LIKE conditions with OR.

Parent topic:SQL Operators and Pattern Matching Functions