Our company, Scribble Software just purchased the tool yesterday and I am trying to build a pretty simple QUERY with a LIKE clause and wildcard. In short, I have two tables that are similar to the examples below
Table1 Table2
CustId Name CustId Name
1 USD - Aaron 5 Aaron
2 CRC - Aaron 6 Luke
3 USD - Luke
4 CRC - Luke
I am trying to build a query that joins to the two tables where Table2.Name is LIKE Table1.Name. Because the columns are not exactly the same, I am using a wildcard in the query. Because the Name in Table2 is always a substring of the Name in Table1 this should work and it is not.
My query looks like the following:
SELECT Table1.CustId as OldId, Table1.Name as OldName, Table2.CustId as NewId, Table2.Name as NewName
FROM Table1, Table2
WHERE Table2.Name LIKE ( '%' + Table1.Name + '%')
I want the result to look like below:
Result
OldId OldName NewId NewName
1 USD - Aaron 5 Aaron
2 CRC - Aaron 5 Aaron
3 USD - Luke 6 Luke
4 CRC - Luke 6 Luke
I have tried many different varieties of that statement to no avail. I am positive that the wildcard and LIKE statement work with the tool for literal strings but, it appears that it is not doing what I desire when combined with the contents of a column.
Any help would be greatly appreciated since I am working on migrating a customer where this query is critical for updating important records. I may have to do it programmatically if this doesn't work and I really am crunched for time.