Geek Answers Handbook

Search a database using scrambled words in SQLite

I am wondering if it can be searched in a database with given scrambled words.

I have a table mobsin the database and it contains the name of the monster names

If the name of the monster is given A Golden Dregonor A Golden Dfigonor A Gelden Dragon, I want it to find A Golden Dragonor matches matches from the database. Usually, one or two letters with max are specified in the same way as scrambling.

Is this possible with only SQL queries? Or do I need to build a query by analyzing a given monster name?

I use LUA for the code side.

+6
source share
3 answers

. JS fuse.js .

, . , .

lua .

function levenshtein(s, t)
  local s, t = tostring(s), tostring(t)
  if type(s) == 'string' and type(t) == 'string' then
    local m, n, d = #s, #t, {}
    for i = 0, m do d[i] = { [0] = i } end
    for j = 1, n do d[0][j] = j end
    for i = 1, m do
      for j = 1, n do
        local cost = s:sub(i,i) == t:sub(j,j) and 0 or 1
        d[i][j] = math.min(d[i-1][j]+1, d[i][j-1]+1, d[i-1][j-1]+cost)
      end
    end
    return d[m][n]
  end
end

, , , , , , . , memoize .

  levenshtein('referrer', 'referrer') -- zero distance
  >>> 0
  levenshtein('referrer', 'referer') -- distance of one character
  >>> 1
  levenshtein('random', 'strings') -- random big distance
  >>> 6

, lua , lua - , .

local monsters = {'A Golden Dragon', 'Goblins', 'Bunny', 'Dragoon'}

function levenshtein(s, t)
  local s, t = tostring(s), tostring(t)
  if type(s) == 'string' and type(t) == 'string' then
    local m, n, d = #s, #t, {}
    for i = 0, m do d[i] = { [0] = i } end
    for j = 1, n do d[0][j] = j end
    for i = 1, m do
      for j = 1, n do
        local cost = s:sub(i,i) == t:sub(j,j) and 0 or 1
        d[i][j] = math.min(d[i-1][j]+1, d[i][j-1]+1, d[i-1][j-1]+cost)
      end
    end
    return d[m][n]
  end
end

--Fuzzy Search Returns the Best Match in a list
function fuzzySearch(list, searchText)
    local bestMatch = nil;
    local lowestScore = nil;

    for i = 1, #list do
        local score = levenshtein(list[i], searchText)
        if lowestScore == nil or score < lowestScore then
            bestMatch = list[i]
            lowestScore = score
        end 
    end

    return bestMatch
end

print ( fuzzySearch(monsters, 'golen dragggon') )
print ( fuzzySearch(monsters, 'A Golden Dfigon') )
print ( fuzzySearch(monsters, 'A Gelden Dragon') )

print ( fuzzySearch(monsters, 'Dragooon') ) --should be Dragoon
print ( fuzzySearch(monsters, 'Funny') ) --should be Bunny
print ( fuzzySearch(monsters, 'Gob') ) --should be Goblins


A Golden Dragon
A Golden Dragon
A Golden Dragon
Dragoon
Bunny
Goblins

SQL

T-SQL, .

SQLlite editdist3, , .

+4

, " ", . lua , :

local mob_name = "A Golden Dregon"--you could do something like, input("Enter mob name:")
local scrambled_dragon_names = {"A Golden Dregon", "A Golden Dfigon", "A Gelden Dragon"}
for _,v in pairs(scrambled_dragon_names) do
  if v == mob_name then
    mob_name = "A Golden Dragon"
    break
  end
end

, !

P.S. , , .

0

- , , . ,

b fulden gorgon

" ". , , , , ,

"A", "G" "n", "D" "n"

LIKE , :

SELECT * FROM mobs WHERE monster_name LIKE 'A G%n D%n';

, . , .

0

More articles:


All Articles