Start Free Trial

asked on

How to join 2 tables in a SQL statement on a field with different value format.

I have 2 tables: A and B.
Both tables have a field named ITEM.
The ITEM format in table A is like: ABCD-ABC12345678
The ITEM format in table B is : ABCDABC12345678 (with no "-")
How can in join this 2 tables based on the ITEM field?

I have tried this way:
SELECT A.ITEM, B.VENDNUM
FROM A LEFT JOIN B ON (REPLACE(A.ITEM,'-','')) = B.ITEM

It works but it does not link the table B.

Thanks,
Jose.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

The result of the statement list the records in table A but not the correspondent records in table B.
The field type is string.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

It is sql statement from visual dataflex programing tool accessing sql server tables.

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial