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