SQL JOIN ON # = chr#

Question

I have a Postgres DB with some very bad schema design, I'm trying to join two tables with and ID and a field which has the ID but with a couple of characters at the beginning, how would I join these?

eg.

TABLE1
------
Field name: ID
Value: 1000

TABLE2
------
Field name: NUMBER
Value: WO-1000

so basically I need it to say something like:

JOIN TABLE2 ON (TABLE1.ID = 'WO-' + TABLE2.NUMBER)

Thanks

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

Use || operator to concatenate two values in Postgres. Try this.

JOIN TABLE2 ON (TABLE1.ID = 'WO-' ||  TABLE2.NUMBER)

If your Number column is of Integer type then cast it to text.

JOIN TABLE2 ON (TABLE1.ID = 'WO-' ||  cast(TABLE2.NUMBER as text)) --or TABLE2.NUMBER::text

Check here for more info

edited May 23 '17 at 10:27

Community

1
1

answered Nov 04 '15 at 16:35

Pரதீப்

88,697
17
124
160

The `||` is not specific to Postgres - this is what has been defined in the SQL standard 30 years ago – a_horse_with_no_name Nov 04 '15 at 17:15
@a_horse_with_no_name - Yep that's a sql standard. Was just mentioning its a way to concatenate :) – Pரதீப் Nov 04 '15 at 18:28

SQL JOIN ON # = chr#

1 Answers1