PostgreSQL SQL syntax - JOIN and substrings

Question

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

How do you know that host2 is the destination host for host1? I don't see how you can make that connection. — user1822
– user1822, Commented Aug 18, 2014 at 14:31
The "bkvol1_DR" volume exists only on host "host2", and is the target of "vol1" because the name "vol1" appears in the volume string. — John
– John, Commented Aug 18, 2014 at 14:42

user1822 · Accepted Answer · 2014-08-18 14:50:30Z

5

select a.host, b.host, b.volume
from hosts as a
  join hosts as b 
    on b.volume like '%'||a.volume||'%' 
   and a.host <> 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 <> b.host
   and position(a.volume in b.volume) > 0

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

answered Aug 18, 2014 at 14:50

user1822

Add a comment |

Stack Exchange Network

PostgreSQL SQL syntax - JOIN and substrings

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL SQL syntax - JOIN and substrings

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions