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?

Mere

You mean LIKE '%abc%' in SQL Server? – Diversiform

yes. I am aware of using like and ilike. But only static text can be passed to like/ ilike. i need to use something like col1 ilike %col2%. Where col1 and col2 are column values. – Mere

Answer 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.

Hamartia

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

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

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

Answer 2

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

position(col2 in col1) <> 0

Endoergic

Answer 3

You can use:

select * from TABLE_NAME where TABLE_NAME like '%a%';

%a% > the results going to show you all the data that contains an a in any position of the value.

also, you can use:

%a > values where ending an a

a% > values starting with a

_a% > values with a in the second position

Doone