Join by [Starts with]

[ciplionej]

I'm trying to join two tables where my matching condition is a field from one table should match the start of a field in the other table.

I could not find such an option in the parameters for the Join tool. I could also not find a tool that seemed like would do this.

The issue is that the length of the field to be matched is variable so I cannot really create a new field with a formula to match that either.

Or else could i make a Join using contains()?

The field in table Left would look like:

Record	Name	Value
1	Sesame street	12
2	Ocean Drive	32
3	Mile End Road	1.5

My table Right would have the following format:

Record	Address	Owner
1	Sesame street 5	Cookie Monster
2	Sesame street 5 - 5th floor	Cookie Monster's mother in law
3	Ocean Drive 12112	Eminem
4	Mile End Road	David Hasselhof
5	Mile End Road 1235B	Luke Skywalker

Any ideas?

Thanks!

[alexnajm · Accepted solution]

Sounds like a Find Replace tool! It has "Beginning of Field" capabilities, as well as case insensitivity to help out. Here's a workflow to illustrate:

[Felipe_Ribeir0 · Accepted solution]

Hi @ciplionej 

There are some ways to go around this, one of them is by using an append + contains filter:

[Luke_C · Accepted solution]

Hi @ciplionej 

As others have said, that functionality is not in the join tool. Here's one option

[ciplionej]

Find and Replace is perfect, thank you!

[ciplionej]

Thank you for the quick reply and the proposed solutions with the Append and Filter.

It is a solution but I'm concerned it would not scale well once 1000s of records are on both tables.

I'll try it anyway since it's an elegant solution for other similar situations where Find and Replace just will not cut it.