473,898 Members | 3,458 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I get a partial match?

4 New Member
I have been working on this for a while now and I wanted to see if someone could assist me. I have 2 tables each with 5 fields. 4 of the fields are 2 character strings, then the final field is the full value.
[EX 1:
Table1......... .......Table2
Seg1: 10............. Seg1: 10
Seg2: 11............. Seg2: 11
Seg3: 15............. Seg3: 15
Seg4: 01............. Seg4: Null
Final: 10111501...Fina l: 101115]
[Ex 2:
Table1......... .......Table2
Seg1: 10............. Seg1: 10
Seg2: 11............. Seg2: 11
Seg3: 15............. Seg3: 15
Seg4: 01............. Seg4: 01
Final: 10111501...Fina l: 10111501]

I know how to match the finals where they are equal but I need to match where there is a partial match with the greatest number of Segments. So in the above I would need to exclude the exact match EX 2 and match 10111501 to 101115 like in EX 1, however I have not been able to get this to work.

Any assistance would be greatly appreciated.
Aug 17 '09 #1
16 5211
1,287 Recognized Expert Top Contributor
This sounds very much like an assignment, but are you matching one record from Table1 with a specific record in Table2, or all/any of them? Are you trying to do this in a query?
Aug 17 '09 #2
266 Recognized Expert Contributor
I'm not sure I know exactly what you are asking, you may need to clarify more. But if I were to guess, you could compare the fields, using a IIF function and a LEN function.

Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Seg1, Table1.Seg2, Table1.Seg3, Table1.Seg4, Table1.Seg5
  2. FROM Table1, Table2
  3. WHERE (((IIf(Len([Table1].[Seg5])>Len([Table2].[Seg5]),[Table2].[Seg5]=Left([Table1].[Seg5],Len([Table2].[Seg5])),[Table1].[Seg5]=Left([Table2].[Seg5],Len([Table1].[Seg5]))))<>False));
The code above, compares the length of both fields, then based on which field is longer, it will cut the longer field down to the same length of the smaller field, and then do an equal join on the 2 fields.

Let me know if this makes sense,
Aug 17 '09 #3
4 New Member

Thanks for the information. I am not sure this will work for what I need though. Basically I have 2 tables 1 may have 10111501 and the other may have 101115 without the last segment. I am trying to figure out a way to do this that is efficient and quick. I was able to do it with Sub queries but they take forever to run.

Let e know if this makes sense to you.

Aug 19 '09 #4
8,834 Recognized Expert Expert
You may wish to try the Instr() Function which will specifying the position of the first occurrence of one string within another.
Expand|Select|Wrap|Line Numbers
  1. Debug.print InStr(10111501,101115) 
will equal 1 indicating that 101115 is contained withing 10111501 starting at Position 1.
Expand|Select|Wrap|Line Numbers
  1. Debug.print InStr(10111501,102115 
will evaluate to 0 since 102115 is not contained in 10111501.
Aug 19 '09 #5
4 New Member

I thought about using Instr() but There may be multiple hits for a single match. Let me give another example.

Table 1: Has 10111501
Table 2: Has 10, 1011, 101115

I would need to match 10111501 to 101115 but not 10 or 1011.

Thanks for the help.
Aug 19 '09 #6
266 Recognized Expert Contributor
How about trying the InStr() under an if that checks the length of the string first

Expand|Select|Wrap|Line Numbers
  1. If len(101115) > 4 then
  2.    Debug.print InStr(10111501,101115)
  3. End If 
Aug 19 '09 #7
4 New Member
The only problem is that there are thousands of codes that need to be matched up.
Aug 19 '09 #8
266 Recognized Expert Contributor
One way or another you will need a function to handle each value as it is passed through the function, have you tested this function and see how long it takes? Because you would be surprised how fast some functions work, it all depends on the intensity of the function.
I once wrote a strip string function that i swore would take forever on 500,000 records but once I ran it, it was faster than I had expected it to be.
I welcome anyone with a better idea, but I'm not sure if there is a much better one.

Aug 19 '09 #9
32,584 Recognized Expert Moderator MVP
If, as seems clear, you will need to determine which of the pairs (of records from the two tables) has the best match, then you will need to produce a cartesian product of matches before grouping these and finding the greatest number of sub-field matches.

This will certainly take an enormous amount of processing. The larger the individual tables grow, the longer this will take (not even gradually, but exponentially).

I'm sorry - I see no good news here.

Unless you can change your specification somehow, so that there are some givens to work with and set the indices to, I see no viable solution.
Aug 19 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Paul MG | last post by:
Hi Template partial specialization always seems like a fairly straightforward concept - until I try to do it :). I am trying to implement the input sequence type (from Stroustrup section 18.3.1, 'Iseq'). I want the version for containers that he gives, but also to provide a specialization for construction from a pair<It,It> (eg because that is returned by equal_range()).
by: Erik Wikström | last post by:
In school (no I will not ask you to do my schoolwork for me) we talked about policy-based design and got an assignment where we got the a code- fragment from a stack-implementation. The idea with the code was that if, when its destructor was called, it still contained any elements it would run 'delete' on them if they were pointers and do nothing if they were not. Our assignment was to reimplement the stack using policies so that the...
by: Christian Staffe | last post by:
Hi, I would like to check for a partial match between an input string and a regular expression using the Regex class in .NET. By partial match, I mean that the input string could not yet be complete but I want to know if a match is possible so far. For instance I want to design a text box to enter a date and validate the correctness of the date as the user types character. If the user enters 1953/12/23 it will match my regex of course...
by: Liu, Mingyi | last post by:
Sorry if this question has been asked before. I tried to search in postgres mailing lists at http://archives.postgresql.org/pgsql-general/ just now and it gave me error "An error occured! Can not connect to search daemon". Anyway I installed tsearch2 and openFTS and was able to do some searches. However, when I checked searching partial words, it of course does not work(unless the partial word just happens to be the indexed version). IMHO,...
by: wecka | last post by:
Hello All, Does any one know how to use the form filter in Access to provide partial match? When you choose to "form filter," all text boxes will convert to combo boxes where you can choose from the already stored data in the corresponding tables. But for example I want to match all records containing the word John (assume it is a name field). If there is a way to do this, it will save my life from having to write a big chunck of SQL code...
by: SQL Learner | last post by:
Hi Alex (Kuznetsov) and All, This is to follow up with my last post, "Link two tables using partial word match". How can I UPDATE table using partial word match? How can I write a SQL statement to do so? (I am using MS-Access.) The two tables and the expected result are listed below: Thank you in advance!
by: chungiemo | last post by:
Hi I have a project with access it has 2 non-related tables in access, which should have been related by ID, due to the poor data entry standards I am trying to match records from table 1 to table 2. I hvae tried the simple "Like" operator in SQL but it does not like matching to another table but instead a specified criteria Example Table 1 ID: WIG102898 Firstname: John
by: chungiemo | last post by:
Hi thought I would do another thread as this one is a bit different from the previous problem I am looking for a solution to the relating problem Comparing 2 access databases with 2 tables, they are non-related tables, but should have been related by the ID, and reason for this was due to the poor data entry standards. I am trying to compare both databases with a Partial ID in Table 1 and match it to the Full ID in Table 2 with the...
by: tbucha3 | last post by:
I have two tables that I have to get information from to produce another table. Both tables come from separate resources, one has partial accounts with addresses and the other has complete account numbers with names. I need all of the information on one report and I'm struggling to write an SQL to do a partial number match. Table1 ACCT Address 123 123 street 789 15 crescent Table 2 Acct Name 12345 Tom
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.