Joining on partial matches

Hi all,
I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) I want to select distinct() and
join on id number to return a recordset containing every individual
listed in both the files HOWEVER, in some cases an incomplete ID
number has been collected into one of the 2 files -is there a way to
join on partial matches not just identical records in the same way as
you can select where LIKE '%blah, blah%'??
Is hash joining an option i should investigate?

TIA
MarkA join expression can include any predicates, including LIKE:

...
ON A.colx LIKE B.colx+'%'

You may also find the functions CHARINDEX and PATINDEX useful (see BOL).

--
David Portas
----
Please reply only to the newsgroup
--

"Mark" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.posting.google.c om...
> Hi all,
> I have 2 files containing Id numbers and surnames (these files
> essentially contain the same data) I want to select distinct() and
> join on id number to return a recordset containing every individual
> listed in both the files HOWEVER, in some cases an incomplete ID
> number has been collected into one of the 2 files -is there a way to
> join on partial matches not just identical records in the same way as
> you can select where LIKE '%blah, blah%'??
> Is hash joining an option i should investigate?
> TIA
> Mark|||A join expression can include any predicates, including LIKE:

...
ON A.colx LIKE B.colx+'%'

You may also find the functions CHARINDEX and PATINDEX useful (see BOL).

--
David Portas
----
Please reply only to the newsgroup
--

"Mark" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.posting.google.c om...
> Hi all,
> I have 2 files containing Id numbers and surnames (these files
> essentially contain the same data) I want to select distinct() and
> join on id number to return a recordset containing every individual
> listed in both the files HOWEVER, in some cases an incomplete ID
> number has been collected into one of the 2 files -is there a way to
> join on partial matches not just identical records in the same way as
> you can select where LIKE '%blah, blah%'??
> Is hash joining an option i should investigate?
> TIA
> Mark|||>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<

Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning.|||>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<

Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning.