String similarity with Ruby + Sqlite (Levenshtein distance / edit distance)

install.sh

# see https://github.com/sparklemotion/sqlite3-ruby/issues/383#issuecomment-1473000796

SQLTITE_EXTENSIONS_DIR=/opt/sqlite-extensions # must exist

cd /tmp
git clone https://github.com/sqlite/sqlite.git
cd sqlite
gcc -g -fPIC -shared ./ext/misc/spellfix.c -o spellfix.o
sudo mv spellfix.o $SQLTITE_EXTENSIONS_DIR
rm -rf sqlite # clean up

ruby-sqlite-levenshtein.rb

# see https://github.com/sparklemotion/sqlite3-ruby/issues/383#issuecomment-1473000796
# adapted from https://stackoverflow.com/questions/49779281/string-similarity-with-python-sqlite-levenshtein-distance-edit-distance/49815419#49815419

require 'sqlite3'

ext_dir='/opt/sqlite-extensions' # or whereever you keep the extensions

db = SQLite3::Database.new(':memory:')
db.enable_load_extension(true)
db.load_extension(File.join(ext_dir , "spellfix.o"))
db.execute("CREATE VIRTUAL TABLE demo USING spellfix1;")
db.execute('CREATE TABLE mytable (id integer, description text)')
db.execute('INSERT INTO mytable VALUES (1, "hello world, guys")')
db.execute('INSERT INTO mytable VALUES (2, "hello there everybody")')
result = db.execute('SELECT * FROM mytable WHERE editdist3(description, "hel o wrold guy") < 600')
puts result