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

Re: Question on joining tables

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 (view raw or flat)
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?


In response to

pgsql-novice by date

Next:From: Oliver ElphickDate: 2002-06-04 13:08:14
Subject: Re: Incorrect date/time returned by current_timestamp
Previous:From: Tom AnsleyDate: 2002-06-03 18:48:24
Subject: JDBC issue

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