inner join and remove leading and trailling letters

[zwasdl]

I have two tables, one(A) has a column (c1) looks like N789SP, another
table(B) has a column (c1) looks like 789. How do I join these two
tables on above columns?

I tried the following two queries:

select a.c1, b.c1
from a inner join b on instr(a.c1, b.c1)>=1

select a.c1, b.c1
from a, b
where instr(a.c1, b.c1)>=1

Access just hangs forever.
How could I build the right query?

Another question: How can I create a colum in A by removing leading and
trailing letters in a.c1, and keep only numbers (length may vary)?
for example, N789SP->789, S12347D->12347

thanks a lot,

Wei

 

[pi3832]

Another question: How can I create a colum in A by removing leading and

trailing letters in a.c1, and keep only numbers (length may vary)?
for example, N789SP->789, S12347D->12347

Using Val() will automatically trim the subsequent letters off. Is
there a constant number of proceding letters?

 

[John Nurick]

Probably you need the LIKE operator. Depending on just what your values
are, it might be
A INNER JOIN B
ON A.c1 LIKE 'N' & B.c1 & 'SP'
or
ON A.c1 LIKE '[A-Z]' & B.c1 & '[A-Z][A-Z]'
or whatever.

 

[zwasdl]

Yes, only one letter proceding. So I used val(mid(a.c1, 2)), and it
works.

Thank you!

Wei