CODEWITHSUNDEEP
sqlpostgresql
Samuel Donadelli
Samuel Donadelli

Reputation: 347

Does a column contain certain part of the other column string? (postgres)

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

Table emaillist: 

    complete_email
---------------------------------
    [email protected]
    [email protected]
    [email protected]
    [email protected]
    [email protected]

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
---------------------------
    [email protected]
    [email protected]
    [email protected]

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

Thanks, Samuel

Upvotes: 1

Views: 2072

Answers (2)

Bohemian
Bohemian

Reputation: 425013

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.

Upvotes: 2

Linger
Linger

Reputation: 15058

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 || '%';

Upvotes: 5

Related Questions