1

I would like to check if a column string is contained in the other column.

Table emaillist: 

    complete_email
---------------------------------
    zazumba@hotmail.com
    jere@yahoo.com
    maryhelp@aol.com
    neil@cde.de
    john_smith@zodll.ne

Table emailpartial:

    username
--------------------
    zazumba
    maryhelp
    neil

The query I want to perform: 

select e.complete_email 
   from emailslist e, emailpartial ep 
      where e.complete_email like '%ep.username%';

I would expect a result like: 

    complete_email
---------------------------
    zazumba@hotmail.com
    maryhelp@aol.com
    neil@cde.de

However, it is not the right way of writing this sql query. Can someone clarify how can I write it?

Thanks, Samuel

Samuel Donadelli
  • 347
  • 1
  • 3
  • 12

2 Answers2

5

I do believe what you are looking for is:

select e.complete_email 
from emailslist e, emailpartial ep 
where e.complete_email like '%' || ep.username || '%';
Linger
  • 14,942
  • 23
  • 52
  • 79
2

You can use the built in function strpos():

select e.complete_email 
from emailslist e
join emailpartial ep 
  on strpos(e.complete_email, ep.username) > 0

which avoids unnecessary concatenation required if like is used.

Note that it's best practice to code joins using proper join syntax.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • This is more elegant like you said and I would pick this for most purposes. I tested and found that `LIKE` is twice as fast in seq scans, though (v9.3). More importantly, `strpos()` can't use an available [trigram index (as opposed to `LIKE`)](http://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations/13452528#13452528). – Erwin Brandstetter Jul 29 '14 at 16:11