From: | Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Chris Pizzo <chris(at)artinside(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Question on joining tables |
Date: | 2002-06-03 21:27:38 |
Message-ID: | 1023139658.12576.42.camel@kant.mcmillan.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tue, 2002-06-04 at 05:06, 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 you are using CHAR(NN) fields your values will be space padded.
This is probably what is causing the problem.
Try something like:
select some_data from A,B where (trim(substr(A.field,4,12)::TEXT) =
trim(substr(B.field,4,12)::TEXT));
Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2002-06-04 13:08:14 | Re: Incorrect date/time returned by current_timestamp |
Previous Message | Tom Ansley | 2002-06-03 18:48:24 | JDBC issue |