I am trying to join two tables based off of two columns that have been combined (often imperfectly) into another tables column. I am trying to join the tables so the correct records are associated with each other, so I can compare the FDebit and the Debit fields.

The FMEMO is normally generated by taking the Num, then adding a space, then adding the Memo text. As you can see below, our process is not yet perfect. I would therefore like to match the Num, then the space, then the first 10 characters of the Memo field to the FMEMO field.

I have included code below with sample data. Could you please offer me some suggestions on how to accomplish this?

Invoices table

MEMO Num DEBIT

Supplies. Soto Cano 1135 2.25

Suction Hose (1-1/2") by the food 3 74.04

Hose/Tubing:Braided Hose (1") by the food 3 98.72

QP10 Meyers surface pump (60hz) 3 206.27

Cage including f tank, alum parts box and 2 buckets of alum 3 752.03

Cage including valve manifold, F1 & F2 3 3774.08

cage with IBC in it 1135 268.41

Pvc accesories for installation of LWTS. 1175 4.26

Pvc accesories for installation of LWTS. 1175 27.26

Expenses table

FMEMO FDebit Supplies. Soto Cano 41.8 2.25

3 Suction Hose (1-1/2 74.04

3 Hose/Tubing:Braided Hose (1 98.72

3 QP10 Meyers surface pump (60hz) 3970 206.27

3 Cage including f tank, alum parts box and 2 buckets of alum 14474 752.03

3 Cage including valve manifold, F1 & F2 72638 3774.08

3 cage with IBC in it 5166 268.41

1175 Pvc accesories开发者_如何学Go for installation of LWTS. 82.03 4.26

1175 Pvc accesories for installation of LWTS. 524.67 27.26

Code to replicate:

CREATE TABLE #tempExpenses (
FMEMO varchar(Max), FDebit money)

CREATE TABLE #tempInvoices (
MEMO varchar(Max), Num integer, DEBIT money)

INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('Supplies. Soto Cano 41.8', 2.25)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('3 Suction Hose (1-1/2', 74.04)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('3 Hose/Tubing:Braided Hose (1', 98.72)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('3 QP10 Meyers surface pump (60hz) 3970', 206.27)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('3 Cage including f tank, alum parts box and 2 buckets of alum 14474', 752.03)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('3 Cage including valve manifold, F1 & F2 72638', 3774.08)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('3 cage with IBC in it 5166', 268.41)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('1175 Pvc accesories for installation of LWTS. 82.03', 4.26)<br/>
INSERT INTO #tempExpenses (FMEMO, FDEBIT) VALUES ('1175 Pvc accesories for installation of LWTS. 524.67', 27.26)<br/>

INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('Supplies. Soto Cano', 1135, 2.25)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('Suction Hose (1-1/2") by the food', 3, 74.04)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('Hose/Tubing:Braided Hose (1") by the food', 3, 98.72)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('QP10 Meyers surface pump (60hz)', 3, 206.27)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('Cage including f tank, alum parts box and 2 buckets of alum', 3, 752.03)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('Cage including valve manifold, F1 & F2', 3, 3774.08)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('cage with IBC in it', 1135, 268.41)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('Pvc accesories for installation of LWTS.', 1175, 4.26)<br/>
INSERT INTO #tempInvoices (MEMO, Num, DEBIT) VALUES ('Pvc accesories for installation of LWTS.', 1175, 27.26)<br/>

SELECT *
FROM #tempExpenses 

SELECT *
FROM #tempInvoices

Well I really hate myself for producing this TSQL but I think this is what you are looking for:

SELECT *
FROM #tempInvoices i
INNER JOIN #tempExpenses e ON CAST(Num as varchar(10)) + ' ' + SUBSTRING(MEMO,1,9-LEN(CAST(NUM as varchar(10)))) = SUBSTRING(FMEMO,1,10)

Which concatenates the number and takes so many characters form the field i.e if 3 then 9, if 1111 then 9-4 and joins with the same amount of characters form the other table.

Of course this is a very inefficient and ugly query. I would rather normalize the data in the database (parse clean etc)