By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,917 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,917 IT Pros & Developers. It's quick & easy.

Joining on partial matches

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <ma**@compuchem.co.za> wrote in message
news:63**************************@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

Jul 20 '05 #2

P: n/a
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" <ma**@compuchem.co.za> wrote in message
news:63**************************@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

Jul 20 '05 #3

P: n/a
>> 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.
Jul 20 '05 #4

P: n/a
>> 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.
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.