Skatterbrainz Skatterbrainz - 3 months ago 8
SQL Question

SQL JOIN using Concatenated Field

I have two tables, Table1 contains a column with what constitutes a partial value of a column in Table2, for example:


Table1.XName = "123456"
Table2.ZName = "ABC-123456"


I need to create a JOIN that matches these up, but with MS-SQL 2008 I'm having trouble making this work. Here's a sample of my attempt:


SELECT * FROM Table1
LEFT OUTER JOIN Table2 ON ('ABC-'+Table1.XName)=Table2.ZName


It doesn't matter what type of JOIN I use, or in which direction either, it fails. I know I'm doing something boneheaded here so if anyone can point me in the right direction I would appreciate it.

Answer

This works for me:

DECLARE @Table1 TABLE (XName VARCHAR(200))
DECLARE @Table2 TABLE (ZName VARCHAR(200))

INSERT
INTO    @Table1
VALUES  ('123456')

INSERT
INTO    @Table2
VALUES  ('ABC-123456')

SELECT  *
FROM    @Table1
LEFT JOIN
        @Table2
ON      ZName = 'ABC-' + XName

---

123456  ABC-123456

Could you please post the definitions of your tables and the error message you get?