Partial string match two columns

[kdmrr]

I have two tables, each with a string that represents a model number. I need to create a table that shows each model next to the other. One table contains the full model number and the other table has varying numbers of characters missing. The match needed is to match part of the string in the full model to all of the string in the shorter string.

New to sql, have no idea what vba does. Have a fair understanding of access qbe. Version is 2013.

Thanks in advance.

[jimatqsi]

Look at the INSTR() function. In

INSTR(A,B)
 

returns a number greater than zero, then string B can be found in string A.

Jim

[kdmrr]

Thank you. I don't know anything about this but I'll try it later today and let you know how I do.

[kdmrr]

Jim, Thanks for your reply -I'm sorry, I just don't get it.

Here's a better example of what I'm trying to match

column1 Columm2
test cart
grapey est
afcarty ape

the desired result:

afcarty cart
grapey ape
test est

Thanks much

[NeoPa]

Well, this could have been expressed a lot more clearly with a little effort, but if I understand the question correctly, and I'm really not certain of that, then the following SQL should represent a query that will return what you're after :

SELECT [A].[Column1]
     , [b].[Column2]
FROM   [TableA] AS [A]
     , [TableB] AS [b]
WHERE  ([A].[Column1] Like '*' & [b].[Column2] & '*')

[kdmrr]

NeoPa, Thanks for the response.
I tried the query and couldn't get it to work. Here's a better attempt to explain what I need.

I have two columns of part numbers from two different sources but they represent the same part. I'm trying to match them (there are hundreds and I'm trying to get them on the same row. Thanks

456abc abtest4
part23 3456abcdef
test trpart234y

Below would be the end result of the query

Partial_Model_Num Full_Model_Num
456abc 3456abcdef
test abtest4
part23 trpart234y

[NeoPa]

So, with that requirement, why is what I already suggested not suitable?

[kdmrr]

That's a hopeful comment. I'll keep trying. The problem apparently is that I'm doing something wrong. Thanks

[Rabbit]

It would help if you posted what you tried so we can tell you which part is incorrect

[kdmrr]

Rabbit, here it is

SELECT [One].[Partial_Model]
     , [Two].[Full_Model]
FROM  [tbl_One] AS [One]
    , [tbl_Two] AS [Two]
WHERE ([ONE].[Partial_Model] Like '*' & [Two].[Full_Model] & '*')

It returned a populated "partial_model" and null for "full_Model"

thx

Thanks

[NeoPa]

It seems you have the Like back-to-front. The full should be like the partial but with optional extras. Not the other way around.

[kdmrr]

It worked!...at least on my little test tables. Here is the real sql and it doesn't work. It asks for a parameter value.

SELECT [Ahri].[Ahri_Mn_Trunc]
     , [Gensco].[Gensco_Pn]
FROM   [Ahri] AS [Ahri]
     , [Gensco] AS [Gensco]
WHERE  ([Gensco].[Gensco_Pn] Like '*' & [Ahri].[Ahri_Mn_Trunc] & '*');

Thanks again!

[NeoPa]

Please remember to use the [CODE] tags when posting code. It's mandatory on this site.

As for your error, it would be very helpful if you'd include the prompt when you tell us that it prompts you for a value. This is likely to be something specific to your database, almost certainly a name spelled wrong, so we have no idea what the problem might be unless you include the information.

[kdmrr]

NeoPa,

THANK YOU! I found the problem and the query appears to work.
I was inaccurately typing a field name. I'll get my naming conventions cleaned up.

I don't understand what a [code] tag is. I did read the faq. Is the tag just the bracketed code that is not a reserved word or command?

Thanks again!

[NeoPa]

Hi there. I've reset the Best Answer for you. It's very rarely appropriate to award this to the OP (yourself), and certainly not for this thread. Never mind that. I've sorted it for you.

[CODE] tags are what is used to format code in a way that is more easily readable. When typing out your post you can select a section of it that, includes the code, and then click on the button saying [CODE]. It will then put [CODE] & [/CODE] around that text and it will be formatted into a code block. For more options see BB Code.