SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 06:33 PM
| ||||
| ||||
 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 Mark      |
|
02-28-2008, 06:33 PM
| |||
| |||
| Re: Joining on partial matches 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 |
|
02-28-2008, 06:34 PM
| ||||
| ||||
| Re: Joining on partial matches >> 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. |
Linear Mode
