PostgreSQL SQL syntax - JOIN and substrings I have a single table of hostnames and NFS exports, some but not all of which are related.
Example entries:


    HOST   VOLUME     
    -----  ---------- 
    host1  vol1
    host1  vol2
    host1  vol3
    host2  bkvol1_DR
    host2  bkvol2_DR
    host2  bkvol3_DR


I'm trying to create a query which will give me the following:


   SRCHOST  DSTHOST  SRCVOL    DSTVOL
   -------  -------  ------    ------
   host1    host2    vol1      bkvol1_DR
   host1    host2    vol2      bkvol2_DR
   host1    host2    vol3      bkvol3_DR


I've tried various incantations of substring(), trim(), and join clauses, and I'm getting nowhere. This is on PostgreSQL 9.2. In non-functional code, this is what I want:


select a.host, b.host, a.volume, b.volume from table as a, table as b 
    where a.volume is a substring of b.volume
select a.host, b.host, b.volume
from hosts as a
  join hosts as b 
    on b.volume like '%'||a.volume||'%' 
   and a.host &lt;&gt; b.host;


Instead of LIKE you can also use position which might be easier to read:

select a.host, b.host, b.volume
from hosts as a
  join hosts as b 
    on a.host &lt;&gt; b.host
   and position(a.volume in b.volume) &gt; 0 


SQLFiddle: http://sqlfiddle.com/#!15/a67c5/1