SQL string matching question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

raj - 01 Mar 2008 18:01 GMT

hello
i need a way to match strings from one table on to another table. I am using
the "like"
but its not working correctly.
SELECT [PrimaryKey],[StockSymbole],[StockName] FROM [StockInfo]
inner join SectorInfo on StockInfo.[StockName] like SectorInfo.[StockName]

but this is very slow and it only matches the entire string of course
ignores case. but if i have string

like "Superior Essex" vs. "Superior Essex Inc." or "Air Products &
Chemicals Inc." vs. "Air Products & Chemicals, Inc." i get no matches.

so if i do this manually for "Air Products & Chemicals Inc."

SELECT [PrimaryKey],[StockSymbole],[StockName] FROM [StockInfo]
where [StockPrimaryName] LIKE '%Air prod%'

i get a great match. Can someone please give me a suggestion how can i
accomplish the task of string matching.

thanks

Reply to this Message

Mike C# - 02 Mar 2008 17:12 GMT

Is this a one-off task or do you have to do it many times over and over
again?

> hello
> i need a way to match strings from one table on to another table. I am
[quoted text clipped - 18 lines]
>
> thanks

Reply to this Message

Randeep Sawhney - 08 Apr 2008 16:52 GMT

You shoudl NEVER EVER do a join using Like Operator.

As Mike stated - if it is a one off thing, you may do foillowing:

SELECT [PrimaryKey],[StockSymbole],[StockName] FROM [StockInfo]
inner join SectorInfo on StockInfo.[StockName] like '%' +
SectorInfo.[StockName] + '%'

BUT if this query will be run quite ferquently and needs to perform better
then, you must try to change your db structure and make use of fulltext
indexing if required to search text. Like operator is only suitable for small
resultsets and definitely not for doing joins.

If you want advice on db design then please paste your db structure and
provide as much info about the fields as possible.

cheers
Randeep

> hello
> i need a way to match strings from one table on to another table. I am using
[quoted text clipped - 18 lines]
>
> thanks

Reply to this Message