Regex: Find highest # in a field

[GeekSupport]

Hi,

I'm trying to grab titles which has the pattern "copy (#) of $survey" in survey.title ($survey will be a variable in PHP).

Titles:
-------
You in 10 years
copy (1) of You in 10 years
copy (3) of You in 10 years
copy (1) of another survey
copy (5) of another survey
another survey

I tried doing

SELECT survey.title regexp "^(copy \()[0-9]*(\) of You in 10 years)$"
FROM survey
WHERE survey.rel_uid = 4

but that doesn't seem to work (my regex returns 0's for all titles)

[edit]
Strange,

I can't even do a literal

SELECT survey.title, survey.title regexp "copy \(1\) of You in 10 years"
FROM survey
WHERE survey.rel_uid = 4

wont work either

[edit2]
Okay, that's strange, i had to double escape

(working query below)

SELECT survey.title, survey.title regexp "^(copy \\()[0-9]*(\\) of You in 10 years)$"
FROM survey
WHERE survey.rel_uid = 4

[jofa]

If you want only the row with the highest number, can't you use this?

SELECT title
FROM survey
where title regexp "^(copy \\()[0-9]*(\\) of You in 10 years)$" = 1
order by right(concat("0000", mid(title, 7, instr(title, ") of You in 10 years") - 7)), 4) desc
limit 1

[GeekSupport]

it shows my first copy of

Copy (1) of You in 10 Years

This is my solution

    // Finds next# to enter the survey "Copy of (#) $title"
    function next_copy_num($title)
    {    
        // strips "copy (#) of " from the title
        $_SESSION['title'] = $regex_title = eregi_replace('^(copy \()[0-9]*\) of ',null,$title);
        
        $SQL = 'SELECT title, title regexp "^(copy \\\()[0-9]*(\\\) of '.$regex_title.')$" as regex
                        FROM survey
                        WHERE rel_uid = '.$_SESSION['user'].'
                        HAVING regex > 0';
                    
        $result = mysql_query($SQL);
                
        // retrieves all survey titles that match the pattern: copy (#) of $title
        $count = 0;
        while ($sql_assoc = mysql_fetch_assoc($result))
        {    
            $count++;
            $db_title[$count] = $sql_assoc['title'];    
        }
        
        // finds the largest # of all the retrieved titles        
        while ($count) // uses $count from the mysql_fetch
        {    
            // doesn't allow user to make more than 99 duplicates
            if ((int) substr($db_title[$count],6,2) == 99)
                return 0;
            if ((int) substr($db_title[$count],6,2) > $num)
            {                
                $num = (int) substr($db_title[$count],6,2);    
            }
            $count--;
        }
        
        return $num+1;
    } 

[GeekSupport]

why does regexp require different number of escapes for types of characters?

ex:
\" (1) to escape double quote
\\) (2) to escape parenthesis
\\\\ (3) to escape back slash

SELECT form.title, form.title regexp "^(copy \\()[0-9]*(\\) of Copy \\(1<b>\\) of\\\\ fi</b>rst for\"m)$" as regex
FROM form
WHERE form.rel_uid = 9
HAVING regex > 0

it looks for Copy (1) of Copy (1<b>) of\ fi</b>rst for"m