Re: Question on joining tables

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(dot)Elphick(at)lfix(dot)co(dot)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