wildcard symbols around a field in a join?

[Ben8765]

Hi,

This is my SQL QUERY:

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME;

The issue is that Table2 has NAME values that should join with NAME values
in Table 1, but have extra characters.

Example:

Table1
-----------
NAME
----
RITSON
KING
STEVENSON


Table2
-----------
NAME
----
RITSON (NORTH LIMIT)
KING (WEST LIMIT)
STEVENSON (SOUTH LIMIT)

I'd like the values to join, even though they don't exactly match.

Is it possible to have a wildcard symbols around a field in a join?

This is what it would look like in my head: (look for the *'s)

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *;

-Ben

 

[Jerry Whittle]

Try this if the separator is always the ( :

SELECT Table1.[NAME], Table2.[NAME]
FROM Table1 LEFT JOIN Table2 ON Table1.[NAME] =
Trim(Left(Table2.[NAME], Instr(Table2.[NAME],"(")-1))

 

[Bob Barrows]

Hi,

This is my SQL QUERY:

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME;


I'd like the values to join, even though they don't exactly match.

Is it possible to have a wildcard symbols around a field in a join?

This is what it would look like in my head: (look for the *'s)

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *;

Just delimit the wildcards and change = to LIKE and go with it.

ON Table1.NAME LIKE '*' & Table2.NAME & '*';

You won't be able to view this query in Design View, but don't let that
stop you. What should be concerning you is performance, since the query
engine will not be able to use an index to speed this up.

 

[vanderghast]

You can use any operator (even user defined function returning Boolean) in a
join:

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON TRIM(Table1.NAME) = TRIM(Table2.NAME);


It is slower, though, than if you don't have to, since now, the index cannot
be used.


Vanderghast, Access MVP

 

[John Spencer]

SELECT Table1.NAME, Table2.NAME
FROM Table2 RIGHT JOIN Table1
ON Table2.NAME LIKE Table1.NAME & "*";


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Hi,

This is my SQL QUERY:

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME;

The issue is that Table2 has NAME values that should join with NAME values
in Table 1, but have extra characters.

Example:

Table1
-----------
NAME
----
RITSON
KING
STEVENSON


Table2
-----------
NAME
----
RITSON (NORTH LIMIT)
KING (WEST LIMIT)
STEVENSON (SOUTH LIMIT)

I'd like the values to join, even though they don't exactly match.

Is it possible to have a wildcard symbols around a field in a join?

This is what it would look like in my head: (look for the *'s)

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *;

-Ben

You're close, but you need the LIKE operator (which recognizes wildcards), not
the = operator (which requires an exact match). You also had the wildcards on
the wrong table - they need to be on Table1.NAME to match the extra characters
in Table2:

SELECT Table1.[NAME], Table2.[NAME]
FROM Table1 LEFT JOIN Table2 ON Table2.[NAME] LIKE "*" & Table1.[NAME] & "*";

Your example only shows cases where the field values match on the left and
differ on the right - to prevent the query from being bog-slow and probably
not updateable I'd use only the second asterisk, unless you also have values
in Table1 with spinach to the left as well as to the right.

Note that I bracketed NAME - it's a reserved word and should generally NOT be
used as a fieldname; if you do so it's much safer to consistantly bracket it.

 

[Ben8765]

Thanks everyone for all your replies! This has been a huge help.

-Ben

Hi,

This is my SQL QUERY:

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = Table2.NAME;

The issue is that Table2 has NAME values that should join with NAME values
in Table 1, but have extra characters.

Example:

Table1
-----------
NAME
----
RITSON
KING
STEVENSON


Table2
-----------
NAME
----
RITSON (NORTH LIMIT)
KING (WEST LIMIT)
STEVENSON (SOUTH LIMIT)

I'd like the values to join, even though they don't exactly match.

Is it possible to have a wildcard symbols around a field in a join?

This is what it would look like in my head: (look for the *'s)

SELECT Table1.NAME, Table2.NAME
FROM Table1 LEFT JOIN Table2 ON Table1.NAME = * & Table2.NAME & *;

-Ben

You're close, but you need the LIKE operator (which recognizes wildcards), not
the = operator (which requires an exact match). You also had the wildcards on
the wrong table - they need to be on Table1.NAME to match the extra characters
in Table2:

SELECT Table1.[NAME], Table2.[NAME]
FROM Table1 LEFT JOIN Table2 ON Table2.[NAME] LIKE "*" & Table1.[NAME] & "*";

Your example only shows cases where the field values match on the left and
differ on the right - to prevent the query from being bog-slow and probably
not updateable I'd use only the second asterisk, unless you also have values
in Table1 with spinach to the left as well as to the right.

Note that I bracketed NAME - it's a reserved word and should generally NOT be
used as a fieldname; if you do so it's much safer to consistantly bracket it.