Skip site navigation (1) Skip section navigation (2)

Re: Question on joining tables

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Chris Pizzo <chris(at)artinside(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Question on joining tables
Date: 2002-06-04 13:18:45
Message-ID: 1023196725.20096.3559.camel@linda (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Mon, 2002-06-03 at 17:50, Chris Pizzo wrote:
> Hi,
> I have an interesting issue where i need to join two tables where the field that jions them is similar but not identical.  Table A field data is char(12) and contains data that looks like:
> BBB12345
> BBB345
> BBB4980
> BBB3455
> Table B field data is char(32) and contains data that looks like:
> MMM12345
> BBB345
> BBB4980
> MMM3455
> I need a way to equate  MMM12345 to BBB12345 in the join.  I tried using substrings but this fails ie.
> select some_data from A,B where (substr(A.field,4,12) = substr(B.field,4,12));
> anyone point me in the right direction?

Since CHAR(x) pads its result to x with spaces, the two substrings do
not produce the identical results you expect:

    junk=# select substr(a.field,4,12) || 'xx';
     12345    xx
     345      xx
     3455     xx
     4980     xx
    (4 rows)
    junk=# select substr(b.field,4,12) || 'xx';
     12345       xx
     345         xx
     4980        xx
     3455        xx
    (4 rows)

There are not 12 characters available in the substring for table A, but
only 9.

Possibilities: restrict the comparison to 9 characters; or cast the
results to integers, if the data allows it; or change the tables to use
Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight                    
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "But without faith it is impossible to please him; for 
      he that cometh to God must believe that he is, and 
      that he is a rewarder of them that diligently seek 
      him."       Hebrews 11:6 

In response to

pgsql-novice by date

Next:From: Peter AlbererDate: 2002-06-04 14:04:59
Subject: Using case in an update statement ?
Previous:From: Oliver ElphickDate: 2002-06-04 13:08:14
Subject: Re: Incorrect date/time returned by current_timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group