Help Please Just Purchased: Column LIKE Another Column

Forum for DBF Commander discussing.

Help Please Just Purchased: Column LIKE Another Column

Postby aaronmajor » Thu May 05, 2011 6:52 pm

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.
aaronmajor
 
Posts: 3
Joined: Mon Apr 04, 2011 6:27 pm

Re: Help Please Just Purchased: Column LIKE Another Column

Postby Support » Thu May 05, 2011 10:22 pm

Hi aaronmajor, thanks for your question!
Alas, but this construction doesn't work on the Visual FoxPro provider. To get the detailed description of the LIKE() function, please refer to:
http://msdn.microsoft.com/en-us/library/aa977930%28v=VS.71%29.aspx
As you can see, the only "hard coded" strings can be used in the LIKE clause. You cannot compare or join two tables into one with the LIKE() function.

But you can to try the following:
1. Modify your Table1: cut off (with UPDATE clause and SUBSTR() function) the Name field. In other words, you need to make data in Name field equal to the data of the Table2's Name field.
2. After that, you can to jon your tables into one table with the simple equality "=", not the LIKE() function.

For example:
1. UPDATE table1 SET Name=SUBSTR(name,6,100) <-- removes "USD - ", "CRC - ", and so on.
2. SELECT * FROM table1, table2 WHERE table2.name = table1.name <-- join two tables into the new one

Hope this helps. If not, please attach the tables here. We'll try to find another way :)
Feel free to ask a question - I'll try to help in anyway!
User avatar
Support
 
Posts: 31
Joined: Mon Feb 07, 2011 10:16 am


Return to DBF Commander Professional

Who is online

Users browsing this forum: No registered users and 8 guests

cron