"Jean-Christophe Deschamps" <[email protected]>
schrieb im Newsbeitrag news:[email protected]...
> Perhaps the best way is practice: what's the way to find
> this guy named Éric or is it éric, or Eric, or eric?
> He lives in MÜNCHEN, München, MUNCHEN,
> Munchen or ... Munich.
IMO that's not that much a locale-problem - for these kind of
"unsharp-queries" one can use (but then usually requiring a
fulltable-scan) appropriate userdefined functions like e.g.
a Ratcliff/Obershelp-Algo or Metaphone or something
similar.
Here comes a short code-snippet, what my results are
regarding your example-case from above, using the
Ratcliff-Implementation of my VB-Wrapper.
First the results for different search-terms:
(ID, City, Name is the Tbl-Content - RcCity, RcName
and RcSum are calculated Ratcliff-Results [0 - 100])
for City="munchen" and Name="eric"
ID City Name RcCity RcName RcSum
2 Munchen Eric 100 100 200
3 MUNCHEN eric 100 100 200
1 München Erich 79 67 146
4 Munic Éric 45 96 141
9 Moskau Sergej 27 28 55
8 London Paul 27 21 48
6 Rome Milan 30 14 44
7 Madrid Jose 11 21 32
10 Moscow Alexey 11 10 21
5 Paris Serge 0 14 14
for City="moscow" and Name="sergey"
ID City Name RcCity RcName RcSum
9 Moskau Sergej 50 87 137
10 Moscow Alexey 100 36 136
5 Paris Serge 14 94 108
7 Madrid Jose 17 32 49
4 Munic Éric 32 10 42
8 London Paul 33 0 33
1 München Erich 9 14 23
2 Munchen Eric 11 10 21
3 MUNCHEN eric 11 10 21
6 Rome Milan 12 0 12
Ok, here comes the used data and the query-example:
(sorry, just a Copy'nPaste from a small test-project -
no C-Code - just that you get the idea, how the queries
could look like and what the small test-set was)
Set Cnn = New cConnection
Cnn.CreateNewDB 'without Param creates an InMemory-DB
Cnn.Execute "Create Table T(ID Integer Primary Key, City Text, Name Text)"
'first a few similar sounding inserts
Cnn.Execute "Insert Into T(City, Name) Values('München', 'Erich')"
Cnn.Execute "Insert Into T(City, Name) Values('Munchen', 'Eric')"
Cnn.Execute "Insert Into T(City, Name) Values('MUNCHEN', 'eric')"
Cnn.Execute "Insert Into T(City, Name) Values('Munic', 'Éric')"
'and a few different ones
Cnn.Execute "Insert Into T(City, Name) Values('Paris', 'Serge')"
Cnn.Execute "Insert Into T(City, Name) Values('Rome', 'Milan')"
Cnn.Execute "Insert Into T(City, Name) Values('Madrid', 'Jose')"
Cnn.Execute "Insert Into T(City, Name) Values('London', 'Paul')"
Cnn.Execute "Insert Into T(City, Name) Values('Moskau', 'Sergej')"
Cnn.Execute "Insert Into T(City, Name) Values('Moscow', 'Alexey')"
'now the Ratcliff-based search
Dim SQL As String, Cmd As cSelectCommand
'define a Select-Command
SQL = "Select *, (RcCity + RcName) As RcSum From" & _
" (Select *" & _
", Ratcliff(City,@City) As RcCity" & _
", Ratcliff(Name,@Name) As RcName" & _
" From T Order By (RcCity + RcName) Desc Limit 10)"
Set Cmd = Cnn.CreateSelectCommand(SQL)
'let's set the two Text-Params on the Cmd-Object and Execute
Cmd.SetText Cmd!City, "munchen"
Cmd.SetText Cmd!Name, "eric"
' just another Param-Set
' Cmd.SetText Cmd!City, "moscow"
' Cmd.SetText Cmd!Name, "sergey"
Set Rs = Cmd.Execute()
'dump the results from the Rs-Content
Dim Fld As cField
For Each Fld In Rs.Fields 'enumerate Field-Names
Debug.Print Fld.Name,
Next Fld
Debug.Print
Do Until Rs.EOF
For Each Fld In Rs.Fields 'enumerate Field-Values
Debug.Print Fld.Value,
Next Fld
Debug.Print
Rs.MoveNext
Loop
Olaf
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
