Regular expression in mysql to answer the given query

my mysql table has a column structure that dumps various attributes which is separated by “;” for each of the element of a html file.

+-----------+------------------------------------+
| tag_id    | attributes                         |
+-----------+------------------------------------+
|  1        |class:block_22;id:toc_id_35;        |
|  2        |class:bloack_1000;id:12#4           |
+-----------+------------------------------------+

I want to write a query that selects the rows that have

  1.id attribute that starts with #
  2.id attribute that ends with #
  3.id attribute that has # in between

the first query can be answered this way

SELECT * FROM table where attributes like "%id:#%";

I am not sure of how to handle the other two queries . Please give me some valuable insights.

Answer

These patterns should do what you want:

where attribute regexp 'id:#'
where attribute regexp 'id:[^;]*#(;|$)'
where attribute regexp 'id:[^;]+#[^;]+'

Advertisement

Answer