Text Matching - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Text Matching

Author

Topic

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-06-15 : 09:12:49

Question
I'm tring to work out whether to use LIKE operators, CONTAINS functions or SSIS term extraction and term lookup.
I don't really understand the differences between them. Plase don't try to give me a complete solution to the problem, but if you can give me some pros and cons it will help me decide for myself.

The Project
I am trying to lookup job titles in a dictionary. You can see the sort of problems I'm trying to solve....

The DIC table looks like this:
1 Electrician
2 Cleaner
3 Mechanical Engineer
4 Truck Driver
5 Van Driver
6 Automotive Fitter

The EMPLOYEE table looks like this:
1 Electrician's Mate
2 Cleaning Supervisor
3 Driver
4 Car Mechanic
5 Electrical Engineer

The output will look like this:
Emp_ID Dic_ID
1......1
2......2
3......4
3......5
4......6

My Thoughts
The main problem with a project like this is maintaining a thesaurus. You can go round and round a loop of changing the thesaurus and re-running the code, so speed is very important. Also, changing the thesaurus can have unexpected results that aren't easy to spot.
From what I can see, SSIS is very user friendly but not much flexibility. I's also not suited to complex multi-stage tranformations
Full text search functions are better, but I'm not sr the XML thesauri offer me the flexibility to do conditional replacements or phased replacements.
LIKE operator and REPLACE function seem very flexible and I can get it to run from a thesaurus table, but it's a lot of coding and there's no word-breaker and fuzzy lookup

Any help greatly appreciated.
Thankyou for your time
Jason

Newbie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-15 : 13:23:38

i think seeing your above scenario using LIKE will do the job for you.

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-06-16 : 06:39:36

Thanks - but what are the pros and cons?

Jason

svicky9
Posting Yak Master

232 Posts

Posted - 2009-06-16 : 06:59:34

Like -

You got to do indexing on the columns you may want to use and check the execution plans whether the indexes are used

contains -

is a full text operation and I recommend this for querying the text like dictionary in your case

SSIS lookup

It can be used if you have some good memory on your machine and you can use the upsert method..(Update if exists or else insert)

http://www.sqlserver007.com

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-06-16 : 18:08:58

Thanks SVicky. That's just what I needed to know.

Please could you help me with some SQL?
Here's the thread
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127668[/url]

Jason

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources