Thread: Question on joining tables

Question on joining tables

From

"Chris Pizzo"

Date:

04 June 2002, 11:15:40

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?

Thanks,

Chris Pizzo

Re: Question on joining tables

From

Oliver Elphick

Date:

04 June 2002, 12:23:12

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';
      ?column?
    -------------
     12345    xx
     345      xx
     3455     xx
     4980     xx
    (4 rows)

    junk=# select substr(b.field,4,12) || 'xx';
        ?column?
    ----------------
     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
VARCHAR(x).
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
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

Attachment

signature.asc