Find People Names in Long Text

Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:

Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles inarticle table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)

Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...

Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?

Thanks once again for taking the time.

Cheers,

Tyro

I think that the best way is stored procedure on SQL server side. The question is

Would you like to do it for single article or for all articles in your database at one run?

If for all names in all articles you will kill you server.

Thanks

|||

Thanks for your reply Jpazgier,

Killing the server is what I am worried about, plus the fact that the people's table is highly likely to grow in the future. I was thinking SQL too. doing some sort of lookup and compare, but think that would be very resource intensive.

I was thinking along the lines of probably being able to compare two XML files ... ?, writing the XML files overnight and then comparing with article text before saving the article in db ... just an idea ... what do you think? please feel free to correct me ..

Tyro

|||

I would create stored procedure which will grabperson namefrom your peopletable and will look for each components of this person name in article field, if it will find all components in article I would just report this person as existing inside article and report its ID. The question is you would like to report the person exists in article if one component of his name is in it or all elements should exists in article to report person as connected to it.

I would run it one time for all articles and next every time you add new person or new article, so maybe you need two or three procedures one for searching multiple article with one person , second to search single article with multiple persons and maybe 3rd for startup and maintenance to search all articles with all persons which are currently in database.

I would store results in table with 3 columns ID, articleID and personID so will be easy to insert results and manipulate it in the future.

Thanks

|||

Thanks for all your replies and helping me out with this Jpazgier, Appreciate it. Hopefully what you said should work well.

Tyro