How to find vertices with a property which contains a value inside a curly braces and it follows a pattern?

Question

I created some vertices to represent MTG cards. Each card has a property called mana_cost, which is the cost to play that card in the game. The mana_cost value is stored between curly braces in a string. For example, "{G}{G}" would represent two green mana and "{3}{G}{R}" means that you must pay one red mana (R), one green mana (G) and 3 mana of any color (3).

Here is an example of a creature card I added to the graph:

SELECT * FROM cypher ('MTG', $$
    CREATE (c:Creature {
        name: 'Slippery Bogle',
        set: 'Ultimate Masters (UMA)',
        card_number: 223, rarity: 'Uncommon',
        mana_cost: '{U/G}',
        artist: 'Jesper Ejsing',
        power: 1, toughness: 1,
        type: 'Beast',
        oracle_text: 'Hexproof (This creature can’t be the target of spells or abilities your opponents control.)',
        flavor_text: 'Ogle the bogle, or goggle the boggle? Doesn’t matter. You weren’t going to catch it anyway.'
})
RETURN c
$$) AS (creature agtype);

If I wanted to find cards that had a mana_cost less than or equal to '{2}{G}' and it could find Slippery Bogle (which it's mana_cost is "{U/G} meaning that it can be one green or blue mana") how would I do it?

Answer 1

This answer is 100% my own work.

Assuming that the numbers appear only in the first curly brackets {} of the creatures' mana costs:

SELECT * FROM cypher('MTG', $$
    MATCH (c)
    WHERE (size(c.mana_cost) < 9
            AND c.mana_cost CONTAINS 'G'
            AND (toInteger(substring(c.mana_cost, 1, 1)) <= 2
                OR toInteger(substring(c.mana_cost, 1, 1)) IS null))
        OR (size(c.mana_cost) = 9
            AND c.mana_cost CONTAINS 'G'
            AND (toInteger(substring(c.mana_cost, 1, 1)) <= 1
                OR toInteger(substring(c.mana_cost, 1, 1)) IS null))
    RETURN c.name
$$) AS (card agtype);

The first part of the MATCH WHERE clause takes into account mana costs that have a length less than 9, which is the maximum amount of characters a mana cost can have (for example {R}{U}{G}) that meets the threshold of {2}{G} cards, AKA 3 cards in total with at least one green card. The query first searches for creatures with mana costs that contain the character G to satisfy the requirement of the creatures needing at least one green mana. It then takes the character between the first set of curly brackets {} and converts the string into an integer. It will be matched if the resulting integer is less than or equal to 2. As there are possibilities that the character inside will not be a number but a letter, the search also considers the results that produce null.

The second part of the MATCH WHERE clause takes into account mana costs that have a length of exactly 9. This case requires a slightly different condition to avoid cases such as {3}{U}{G}, which in total exceeds the limit of 3 cards. If the first curly bracket contains a number, the limit would be 1, thus the query matches the resulting integer when it is less than or equal to 1. Converted substrings that produce null have to be considered as well as the character in the first curly bracket may not always be a number.