[SQL]zoeken met wildcards

[XdeckX]

Ik hoop dat iemand me met het volgende probleem kan helpen.

ik heb een db met een tabel waarin posts van een forum worden opgeslagen.
nu wil ik een zoekfunctie gaan maken waarmee er gezocht kan worden in deze tabel.

een aantal eisen waar het aan moet voldoen:

- er moet gezocht kunnen worden op één of meerdere woorden waarbij er gescheiden kan worden door een spatie of een komma
- bij zoeken naar meerdere woorden moeten alle opgegeven woorden voorkomen. (dus niet of de een of de andere maar gewoon alles moet in 1 post staan)
- er moet gezocht kunnen worden op delen van woorden. zoeken op bijv. aa moet matchen met zowel 'aan' als 'vandaag'

het probleem waar ik tegenaanloop is het gebruiken van LIKE. Hierbij is het gebruik van wildcards mogelijk in de vorm van '%'. Punt is alleen dat deze methode bij meerdere woorden de exacte string gaat zoeken (dus de woorden moeten naast elkaar staan). Evenzo is het zoeken naar bijv. het stukje text '50%' niet mogelijk omdat de % hierin wordt gezien als een wildcard.

Het zal er dus op neerkomen dat ik match() en against() moet gebruiken.
Het probleem daarin is alleen dat je hierin alleen een truncate operator hebt (*). Deze kan dus alleen aan het eind gezet worden. (zoeken op 'aa' vind wel 'aandacht' maar niet 'vandaag')

Mijn vraag is dus hoe moet ik mijn SQL query opbouwen zodat deze aan alle bovenstaande voorwaarden voldoet?

[TwoR]

Je kan toch alle zoektermen scheiden op de komma en in een array zetten en aan de hand van de array 1 query laten maken iets zoals:

code:

SELECT * FROM tabel WHERE tekst LIKE '%eerste%' AND tekst LIKE '%tweede%'

Zo kan je dus zoeken op meerdere woorden en alle woorden moeten voorkomen

[Nick_S]

En voor je probleem met escapes van % heb ik het volgende gevonden:

The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more characters, or exactly one character, respectively). In order to interpret them literally, they can be preceded with a special escape character in strings, e.g. "\". In order to specify the escape character used to quote these characters, include the following syntax on the end of the query:

{escape 'escape-character'}

For example, the query

SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}

finds identifier names that begin with an underbar.

Voor het hele verhaal, zie http://www.jguru.com/faq/view.jsp?EID=8881

[Edit: Dit is alleen voor JDBC drivers het geval zie ik nu. Ik weet niet waarin je je SQL probeert uit te voeren maar met zoeken naar "SQL escape character" kom je een hoop tegen.]

[ Voor 16% gewijzigd door Nick_S op 24-10-2005 10:54 ]

[XdeckX]

dat escapen van de % had ik inmiddels ook gevonden.

zit nu op mn werk waar ik dus niets kan checken in PHP. heb uit mn hoofd even wat code uitgetikt... mogelijk werkt dit. Het is wel te lelijk voor woorden maar kan zo snel even niets anders bedenken

PHP:

$string = str_replace("%", "\%", $string); //escapen van %
$string = str_replace("_", "\_", $string); //ecapen van _
$string = addlashes($string); //andere 'foute' dingen escapen
$string = str_replace(" ", ",", $string); //spaties in de zoekstring vervangen door komma

$split = split(",", $string); //zoekstring splitsen op komma zodat alle woorden appart te benaderen zijn.

$aantal = count($split); // hoeveel woorden zijn er ingevoerd door de gebruiker

$query = "SELECT * FROM tabel WHERE posts"; //het begin van de query

// een loopje die op basis van $aantal x wordt uitgevoerd om de $query aan te vullen.
for($i=0; $i<=$aantal-1; $i++)
{
    if($i == $aantal)
    {
        $query .= " LIKE '%".$split['$i']".%'";
    }
    else
    {
        $query .= " LIKE '%".$split['$i']".%' AND posts";
    }
}

als iemand nog iets heeft aan te merken / verbeteren / fouten eruit.... laat maar weten.
en zoals gezegd ik kan het niet checken... ik tik dit even uit mn hoofd.

[Bosmonster]

Tja.. LIKE gebruiken bij dit soort searches.. dan hoop ik dat je forum niet al te veel gebruikt gaat worden. LIKE maakt geen gebruik van indices en moet dus de gehele tabel doorlopen voor iedere query.

Verder geeft het je ook geen informatie over relevantie van de zoekresultaten.

[curry684]

Bosmonster schreef op maandag 24 oktober 2005 @ 12:22:
LIKE maakt geen gebruik van indices en moet dus de gehele tabel doorlopen voor iedere query.

Herstel: LIKE gebruikt alleen indices voor het gedeelte van de matchstring voor het eerste %-teken. SELECT * FROM Table WHERE Field LIKE 'Aap%Mies' gebruikt afaik zelfs in MySQL een eventueel aanwezige index om de resultset te limiteren tot de records waarin Field begint 'Aap' alvorens een full scan te gaan doen.

[XdeckX]

laten we er dan even vanuitgaan dat het forum wel veel gebruik wordt (stellen we een post table met 100.000 rows)

hoe moet ik aan mijn voorwaarden voldoen gebruik makend van indices dan?
ik weet dat like wat aan de trage kant is.
dat is ook de reden dat ik hier deze thread geopend heb. het moet toch mogelijk zijn om dit uit te voeren zonder gebruik te maken van LIKE

[BCC]

XdeckX schreef op maandag 24 oktober 2005 @ 12:34:
laten we er dan even vanuitgaan dat het forum wel veel gebruik wordt (stellen we een post table met 100.000 rows)
hoe moet ik aan mijn voorwaarden voldoen gebruik makend van indices dan?
ik weet dat like wat aan de trage kant is.
dat is ook de reden dat ik hier deze thread geopend heb. het moet toch mogelijk zijn om dit uit te voeren zonder gebruik te maken van LIKE

Dus je wilt doorzoeken, zonder alles te doorzoeken.. grappig Maar om het zaakje te versnellen moet je indexen gaan bouwen.

[ Voor 6% gewijzigd door BCC op 24-10-2005 12:40 ]

[Alarmnummer]

Maar je wilt niet zoeken naar de hele tekst, je wilt zoeken naar keywords. Een standaard db index is dus niet voldoende. Ik weet verder niet veel van de zoekfunctionaliteit van MySQL maar ik bouw regelmatig mbv Lucene dit soort systemen en 100.000 records is peanuts. Je kunt dan zoeken op keywords, maar ook op vervoegingen, dus slapen ipv slaap, fuzzy search (wel kostbaar) en als je wilt kun je ook suggesties doen als ze schrijffouten hebben gemaakt (bv kaamer). Dus als je met Java bezig bent, dan zou ik zeker eens kijken naar Lucene om die db inhoud te laten indexeren.