Postgresql Contains in where clause

Question

Is there a function in postgres like contains ? that can be used in the where clause to check , whether the string passed is contained in column?

Solution 1

There are a bunch of ways of solving this:

1. Use like, ilike, and/or SIMILAR TO along with ||. To handle columns, something like:

   WHERE col1 ilike '%' || col2 || '%';
   

2. Use position as NPE's answer

3. You could also use regexp_matches but that is more complex.

Solution 2

You could use position() for that. It returns zero if the substring is not found:

position(col2 in col1) <> 0

Comments

[vyegorov]

It is possible to use col1 ~ 'abc' or col1 ~ col2 also for this case.

[StartupGuy]

I find that the performance of ~ is much better than LIKE (YMMV)

[mauhiz]

~ is actually a REGEXP. The alias of like is ~~