Showing posts with label searchquot. Show all posts
Showing posts with label searchquot. Show all posts

Tuesday, March 6, 2012

"Fuzzy search" component


Good day.

I'd like to add "fuzzy search" functionality to my application.

"Fuzzy search" in this topic means selecting (from DB table) rows, which have "fuzzy search" coefficient (calculated using etalon string) not less some_predefined_const. Fuzzy search coefficient calculating algorithm can be various.

So with etalon string "Margaret" "fuzzy search" can find "Nargaret", "Margoret", "Margret" etc.

IMHO time to develop, test and tune code must be quite long. I prefer to buy such "fuzzy search" component.

Does anybody know where can I get such component - server version (SQL 2005) or client version (.NET)?
How much can such component cost?

Thanks.

Hello,

Have you looked at the SOUNDEX() function? Although not offering the complete solution, it'll give you something to build upon. For example, if CustName contained Margaret, Nargaret and Margoret, you could:

SELECT * FROM dbo.Customers WHERE SOUNDEX(CustName) LIKE '_626'

This will return all three CustName values.

Cheers,

Rob

|||

No, it won′t. The Naragret is a "n" variation, therefore you probably would need a stronger SOUNDEX functionality like the one that comes with SQL Server. See the following sample to test the soundex:

CREATE TABLE #SomeTable

(

nameCol VARCHAR(50)

)

GO

INSERT INTO #SomeTable

VALUES ('Margaret')

INSERT INTO #SomeTable

VALUES ('Nargaret')

INSERT INTO #SomeTable

VALUES ('Margoret')

SELECT * FROM #SomeTable

WHERE SOUNDEX(namecol) = SOUNDEX('Margaret')

nameCol

--

Margaret

Margoret

(2 row(s) affected)

I would sugegst buying a thrid party component which can be either used from your application code or within the SQLCLR (if you don′t mind using SQLCLR in your SQL Server)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Sure it will:

Code Snippet

create table test(CustName varchar(40) not null)

go

insert into test

select 'Margaret'

union

select 'Nargaret'

union

select 'Margoret'

go

SELECT * FROM dbo.test WHERE SOUNDEX(CustName) LIKE '_626'

|||

Sure, but for the simple way of just passing in values and not just assuming that only the first character is changed, e.g. someone put a typo in there writing Amgaret (which would be A526) instead of Margaret you probabyl would need a real fuzzy lookup instead of only replacing the first char.

I don′t know if that fits the original posters need, but sure your solution fits for this particular case. Maybe I misunderstood the situation or the description was too vague to identify the problem.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi all.

Thank you for answers Smile

I cant use soundex with my problem (example can explain why)

Typical scenario to use "fuzzy lookup" functionality:

0. Initial data loading process inserts into DB table song title and author name: "HUNG UP MADONNA".
There could be some errors during initial data loading process (cause there are no input information quality check).
And song title and author name could be "HANG UP MADONNA" or "HUNG UP MADONA".

1. Manager wants to find this song and view some additional info.
2. He doesn't know what is the right query string "HUNG UP MADONNA" or "MADONNA HUNG UP"
3. Manager enters some similarity coefficient, defining minimal "similarity value".
4. Application retrieves some rows from DB using query string and similarity coefficient.
5. Manager works with the row with max similarity coefficient.

Does anybody know some third party components?
Or how do you think how much can it cost? Its my manager's question Smile.

Sunday, February 19, 2012

"Advanced search" query in SQL Server2000

Hi query-experts,

I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:

Table has the fields: id, fruit, color

User enters: apple pear green

The query should return all records that contain apple AND pear AND green in any of the fields.

Now I do it like this:

SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')

this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.

Question: is there an easier way to achieve this?

(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)

SELECT
tKlant.nKlant AS nKlant,
MAX(sAchternaam) AS sAchternaam,
MAX(sVoornaam) AS sVoornaam,
MAX(sVoorletters) AS sVoorletters,
MAX(sBedrijfsnaam) AS sBedrijfsnaam,
MAX(sPlaats) AS sPlaats,
MAX(sStraat + ' ' + sHuisnummer) AS sAdres,
MAX(sPostcode) AS sPostcode,
MAX(sCommunicatie) AS sCommunicatie,
(CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig,
MAX(sKlantGroep) AS sKlantGroep
FROM tKlant
LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep
LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant
LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant
WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%')
AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%')
AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%')
GROUP BY tKlant.nKlant, DATALENGTH(mOverig)
ORDER BY sAchternaam, sPlaats, sAdres;

you can try something like this:

SELECT

*
FROM table
WHERE fruit in ('apple','pear','green')
and color in ('apple','pear','green')

I recommend you take a look at Erland's article:
http://www.sommarskog.se/dyn-search.html

|||Thanks! I'll check it out