Query SQL on Orientdb with append and substring

Question

I need to retrieve the country from a number having a table with international_prefix and local_prefix like image.

This is a test that I've made but unfortunately won't work.

SELECT *
FROM core_phone_prefix
WHERE (("[phone]").asString()).left(international_prefix.append(local_prefix).length()) = international_prefix.append(local_prefix)

Answer 1

Try these:

With this one I compare only the prefix

select distinct(country) as country from(select international_prefix, "[phone]" as number, country, local_prefix from core_phone_prefix) where international_prefix=number.subString(0,international_prefix.length())

Instead, with this one I compare the international_prefix + the local_prefix with the international_prefix and the local_prefix of the number

select country as country from(select international_prefix, "[phone]" as number, country, local_prefix from core_phone_prefix) where international_prefix.append(local_prefix)=number.subString(0,international_prefix.append(local_prefix).length())

Hope it helps,

Let me know

Answer 2

Can you try with this query

select country from( 
    select number.left(international_prefix.append(local_prefix).length()) as a, international_prefix.append(local_prefix) as b, country from (
        select international_prefix, "[phone]" as number, country, local_prefix from core_phone_prefix
    )
) where a = b

UPDATED

You can insert two index, one on the field international_prefix (NOTUNIQUE_HASH_INDEX) and one on the field local_prefix (NOTUNIQUE_HASH_INDEX )

You can use this query

select expand($a) from (select "[phone]" as number from core_phone_prefix limit 1)
    let $a=(select from core_phone_prefix WHERE international_prefix = $parent.current.number.left(international_prefix.length()) 
            and local_prefix = $parent.current.number.subString(international_prefix.length(),international_prefix.append(local_prefix).length())) limit -1

Let me know