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.

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?

Random guess...

Table1.XName datatype is a number (not character based) so you get conversion errors

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

Try using

SELECT
  *
FROM
  Table1 LEFT OUTER JOIN Table2 ON
    Table1.XName LIKE CONCAT("%", Table2.ZName, "%") OR
    Table2.XName LIKE CONCAT("%", Table1.ZName, "%")

Please mark the answer if that works for you.