SQL LIKE query in Tarantool

user_1391049 0
Viewed 1445

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?

tarantool sql-like sql
3 Answers

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
user_91417790

There is nothing wrong with your query if you use tarantool version 2.x.

SELECT * FROM "your_space" WHERE "smth" LIKE '%some_value%';
user_70484080
Related