regex in mysql select query

Question

One of the column in my table have data like this

@Stream<TTL>@Topic<Abstimmung_Carbon_Accounting>@Date<03-May-2012<@Time<BN,APH>@Participants<MUELLER,RECHTE>@Pages<2>
@Stream<m20>@Topic<Shared_Space_Afternoon>@Date<12.10.12>@Time<Call>@Participants<dehaan>@Pages<2>

What I want is to select the Topic part only, using mysql query. Regex for this is something like Topic\<\w+\>

But how to use it to select part from a field in mysql.

Output needed:

Abstimmung_Carbon_Accounting

Shared_Space_Afternoon

Thanks for your help.

MySQL regexes can match only. You can't capture/extract from a string with them. That will have to be done in client-side code. — Marc B
– Marc B, Commented Dec 10, 2013 at 14:42
If you need to extract data from a column, it will perhaps be useful to add more columns to your table or rethink the structure of your database. — Casimir et Hippolyte
– Casimir et Hippolyte, Commented Dec 10, 2013 at 15:08

Fathah Rehman P · Accepted Answer · 2013-12-11 05:34:11Z

1

You can solve this problem without using regex

Assume you have a table table2 with one column col1 of varchar type and it contain following values

@Stream<TTL>@Topic<Abstimmung_Carbon_Accounting>@Date<03-May-2012<@Time<BN,APH>@Participants<MUELLER,RECHTE>@Pages<2>
@Stream<m20>@Topic<Shared_Space_Afternoon>@Date<12.10.12>@Time<Call>@Participants<dehaan>@Pages<2>

Then you can use following query to get your required output

select substring(col1,Locate('Topic<',col1)+6,(Locate('>',substring(col1,Locate('Topic<',col1)+6))-1)) as result  from table2 
where substring(col1,Locate('Topic<',col1))!=""

answered Dec 11, 2013 at 5:34

Fathah Rehman P

8,8014 gold badges43 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

regex in mysql select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related