SQL partial string match?

[JC419]

I'm trying to match some values from a large table against a smaller one. Table_A has roughly 1,000 rows and Table_B has roughly 600 rows. I know the result should be no more than a few hundred rows, but searching manually through Table_A will take quite a while. I tried the following in Access 2003:

SELECT columnA, others
FROM Table_A, Table_B
WHERE columnA = columnX;

where columnA is from Table_A and columnX is the column from Table_B I want to compare against. This returns roughly 30 matches which is way off. The problem is some of the matches will actually end up being a partial match since they weren't entered the same on both tables. I tried doing the following:

SELECT columnA, others
FROM Table_A, Table_B
WHERE columnA LIKE "%(SELECT SUBSTR(columnX,0,9) FROM Table_B)%";

This returns no results and come to find out Access 2003 doesn't like the 'SUBSTR()' function. An example of the match I'm talking about is having 'ABCXYZ' in columnA and the corresponding value in columnX would be 'ABCXY' or 'ABCXYZ' in columnA maps to 'ABCXXZ' in columnX. Is there any other way to accomplish this?

 

[mikeblas]

Aside from the obvious syntax problem, the query you've written doesn't make sense to me because "SELECT SUBSTR(columnX,0,9) FROM Table_B" could return more than one row. What happens when it returns one million rows? Are you interested in any row from TAble_A where that partial string match happens?

Maybe what you want is this:

 SELECT columnA, others 
FROM Table_A, Table_B 
WHERE columnA LIKE "%" + SUBSTR(columnX,0,9) + "%";

Indeed, it will be very slow. Access won't use an index here because of the unanchored pattern, so it's going to scan all 1000 rows in Table_A, and it's going to do that once for each row in Table_B. That's 600,000 scans.

 

[JC419]

For some reason Access doesn't like Substr() still. I also tried Left() and Mid() from within Access and those both return zero results. If I use Strcmp() with a text comparison, I get the same 30 or so results as I did before. At this point I'm considering ditching Access all together and getting mysql on this machine. Thanks mikeblas!

 

[mikeblas]

Sorry; obviously, I use SQL Server far more than Access. SQL Server has SUBSTR(). Access uses MID, and prefers asterisks to percent signs:

 SELECT columnA, others 
FROM Table_A, Table_B 
WHERE columnA LIKE "*" + MID(columnX,1,9) + "*";

will use 9 characters starting at the first character, and should be the same as LEFT(columnX,9).

Without seeing your data, I can't guess if 30 rows returned is too many, too few, or just right. Similarly, I can't guess why you get zero results with a correctly written query because I don't know what data you've got

You'll need to do a little debugging -- that's going to be far easier than switching databases. It would seem silly to switch just because you can't figure something out. What will you do when you switch to MySQL and get stumped there? Switch back to Access?

 

[JC419]

Ya that would be silly to switch, just a little frustrated with trying to learn Access since I learned on mysql. To be exact, I keep getting the same 37 results. Theoretically, I should have a couple hundred results. The two sets of data are vendor names that I'm trying to hash as I need the complete matches and the partial matches to finish a project. The problem I'm running into are the partial matches, for example it could be Company Name Inc. on Table_A but Company Name on Table_B. It's still a match since the 'Inc.' isn't too important since Company Name still equals Company Name. With that said, I tried what you had with the asteriks instead of '%' and I still get the same 37 results. I figured this or Left() would get me most if not all that I'm looking for. I tried different combinations of starting positions and string lengths with Mid() and no matter what I do, I still end up with the same 37 results. The funny thing I noticed I tried to have it purposely omit one result by putting the starting point past the actual length of the string (length=9, set start to 11) and it still included result. Instr() also produces the same 37 results. This is turning out to be harder than I thought, but in the end it's nice to learn the hard way. Thanks again for your help!

 

[mikeblas]

You explained your problem before, and I believe I understand it. However, without your exact data, I can't guess what's going wrong.

Debugging a query like this one means thinking about the sets of data involved. If you do


SELECT "*" + MID(columnX,1,9) + "*"
FROM TableB


are the results preceisely what you expect them to be? If not, why not? That's half your answer.

Then, from the result set the above query generates, pick one of the items you think should match in your real query. Find the row in TableA that should match. Why doesn't it? What's different? Does it work if you explicitly code it in the query, like this


SELECT columnA, others
FROM Table_A, Table_B
WHERE columnA LIKE "*" + "PartialData" + "*";

? What is the difference? Case? Spacing? Length? Spelling?

If you want, send along your database and I'll do it for you. But it's probably best if you learn to do it yourself. You'll certainly face more challenging queries in your database programming career.

 

[JC419]

Sorry about that, I misunderstood you in that I thought I didn't give enough detail. Anyways, sending the data probably isn't feasible to begin with. I tried doing a simple query to pull the partial data I'd like to try to match and the listing it created is what I'm looking for. If I drop the *'s the data is about as perfect as it's going to get. I picked a couple random pieces of data from that newly created list and compared them to where they would match on Table_A and they seem like they should match when using 'LIKE' as a comparison. So my partial data is where I want it, now I just need to get the matches/partial matches between the tables. Here's what I have so far:

SELECT columnA,others
FROM Table_A, Table_B, Query
WHERE (nstr(1, columnA, Query.name, 1))>0;

where Query is a subquery that has all the partial data and Query.Name is the column in question. This sort of works but it matches each entry in Column_A with every entry in Query.Name (roughly 70,000 matches since there's 700 in Query and about 1000 in Table_A). I think if I can weed out all the copies so it only matches the correct one it should be all set. Thanks a million!

 

[mikeblas]

I picked a couple random pieces of data from that newly created list and compared them to where they would match on Table_A and they seem like they should match when using 'LIKE' as a comparison.

But they're not. Why not?

 

[JC419]

I think I'm on the right path, for some reason it didn't agree with selecting data from a sub query without first sending it to a table. I did the following:

SELECT MID(ColumnX, 1, 9) INTO part_data 
FROM Table_B;

which was then sent to:

SELECT ColumnA others
FROM Table_A, part_data
WHERE (Instr(1, ColumnA, part_column,1))>0;

where part_column represents the column containing the partial data used for searching. I just need to tweak the parameters on the Mid() function so I end up with a precise solution set. Right now it's more or less where I want it, but I noticed a few errors in the solution set. I don't think it's going to be exact, but in this situation as long the majority of the matches are brought out, it can be considered a success. Thanks again for all your help!