function to return regular expression matches in mysql like preg_match return array in php

Question

Hi,

I've table with data like

ticketid  | comments
----------------------
1234      | >
> Ticket ID:TT19027
>
> Ticket Title:report debug
>
>
4567      |>
> Ticket ID:TT19028
>
> Ticket Title:report debug
>
>

I'm looking for a function or query that will return

ticketid  | ticket_no_part_of_comments
1234      | TT19027
4567      | TT19028

I'm able to use clause like

comments regexp 'Ticket ID:([0-9]*)'

but it just returns 1 or 0.

Any suggestions.

Thanks in advance, Anitha

Answer 1

Get substing of ":" in comment column.

You must get string after first ":" character.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

More information about String functions you can find at http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substr

Your solution is:

SELECT ticketid, SUBSTRING_INDEX(comments, ':', -2) as ticket_no_part_of_comments FROM your_table

Answer 2

You might take a look at the preg user-defined functions for MySQL on the MySQL UDF site as there's likely to be something useful there.