inner join and remove leading and trailling letters

  • Thread starter Thread starter zwasdl
  • Start date Start date
Z

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
 
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
Click to expand...

Using Val() will automatically trim the subsequent letters off. Is
there a constant number of proceding letters?
 
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.
 
Yes, only one letter proceding. So I used val(mid(a.c1, 2)), and it
works.

Thank you!

Wei
 
Back
Top