Parse a String for words to match a field in another table

[msndba]

Table a has identity column and a text field
table b has a description and a code

I need to return the code from table b where the best match to that text field in table a exists.

Any suggestions on the best way to accomplish this?

Thanks in advance!

 

[djj55]

Have you tried joining using the soundex function?

 

[Qik3Coder]

Are you trying to identify a row in the other table, or a column in the other table?

This response assumes row.

Performance wise have you thought of creating a mapping table. The initial setup will not be hard, but you will need some form of tool for adding additional records to the A_to_B table, for matches.

Table A
id, Field

Table A_to_B
Field, Code

Table B
Code, Description

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[msndba]

There will be no exact match from the text field of table a to the code description field in b, so I just have to find the best match. To do this, I will need to separate out the words of the text field in a then count the matches against each of the words in the description field of table b, then choose the most hits.

I'm sure I'll have to count spaces, then find the index of each space in both tables, then count the number of words that have a match between the two.

To give you an example,

Table A
ID TEXT
850550 The coffee machine is leaking from the back


Table B
Code Description
1-ACDFJ Not working
1-ACD Major Leak
1-ABFJ No Power
2-ACDFJ Intermittent jams
2-ACD Minor Leak
2-ABFJ Broken Switch
3-ACDFJ Part Damaged

So, the best match would be 1-ACD and 2-ACD as the only matching word or partial word is "leak"

Based on a third field in table A, I would determine if it was a 1- code or a 2- code.