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?

I'd think since Tarantool is NoSQL you won't be able to use SQL syntax for writing queries. — Alex, Mar 14 '16 at 12:37
@Alex, starting tarantool version 2.0 tarantool supports sql — o2gy, Jun 19 '20 at 15:40
Thank you @o2gy, the question is dated 2016, and the first alpha's of version 2 were release few years later. — Alex, Jun 23 '20 at 22:18

bigbes · Accepted Answer · 2016-03-14T13:08:37.057

4

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.

edited Mar 14 '16 at 13:08

answered Mar 14 '16 at 13:00

bigbes

221
1
3

score 4 · Answer 2 · answered Aug 14 '19 at 14:52

4

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

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

answered Aug 14 '19 at 14:52

Dezz

41
1

score 3 · Answer 3 · answered Jun 20 '19 at 13:21

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

SQL LIKE query in Tarantool

3 Answers3