如何在格式不相同的字段上联接两个表

我有两个表,其中包含一个字符串字段,在一个表中该表的长度为6个字节,在另一个表中的长度为7个字节,这是因为在前两个字节和后四个字节之间插入了连字符。这两个字段如下所示:AB1234和AB-1234。我尝试了这样的联接:

    FROM       TableA ta
    INNER JOIN TableB tb ON Left(ta.fld, 2) + '-' + Mid(ta.fld, 3) = tb.fld

...我尝试了

    FROM       TableA ta
    INNER JOIN TableB tb ON Left(ta.fld, 2) = Left(tb.fld, 2) AND Mid(ta.fld, 3) = Mid(tb.fld, 4)

...但是都不行。有没有办法用子查询来做到这一点?还有其他方法吗?

评论
  • Lynch
    Lynch 回复

    How about using REPLACE to remove the dash before comparing:

    SELECT *
    FROM TableA a
    INNER JOIN TableB b
        ON a.fld = REPLACE(b.fld, "_", "");
    
  • jsed
    jsed 回复

    使用RIGHT代替MID

    Left(ta.fld, 2) + '-' + RIGHT(ta.fld, 4) = tb.fld