SQL LIKE query in Tarantool

Question

What is a proper way to make a query in Tarantool DB with SQL LIKE keyword? For example:

SELECT * FROM space where smth LIKE '%some_value%';

Can I search for values using a part of index or I need to write my own LUA script for such functionality?

Answer 1

Yes, you shoud write Lua Script, that'll iterate over space and use lua function gsub on 'smth' field of tuple. There's no way, for now, to search for part of string.

Answer 2

Use stored procedure for optimal prefix-based search. For example, this snippet works with cyrillic texts too:

box.schema.create_space('address')
box.space.address:create_index('prefix', { type = 'tree', parts = { { 1, 'str', collation = 'unicode_ci' } }, unique = true })

select_by_prefix = function(prefix)
    local result = {}
    for _, addr in box.space.address.index.prefix:pairs(prefix, { iterator = 'GT' }) do
        if utf.casecmp(utf.sub(addr[1], 1, utf.len(prefix)), prefix) == 0 then
            table.insert(result, addr)
        else
            break
        end
    end
    return result
end