Select one row without duplicate entries on regex based

Question

My info table has the following columns:

id, comment, status

I want to select all comment from info, and am using the following query :

SELECT name FROM info WHERE status = 1 ORDER BY id

the comment columns has the following content :

id 1 : hello world 2010
id 2 : hello the world 2010
id 3 : hello iam in 2010
id 4 : hello 2020
...

I want to echo all the entries filtred by number (regex number 0-9 4 digit) without duplicates. example for output : id 1 : 2010 id 4 : 2020 Saying: under columns "comment" we have inserted more than 100 row in different value.

`it can not be at the beginning or at the end or half`. So NOT at the beginning, NOT at the end, NOT in the middle? Where can it be then? — Nic3500, Mar 14 '18 at 19:27

score 1 · Answer 1 · answered Mar 14 '18 at 19:26

You really need a function which will extract the year based on a regular expression, like PHP's preg_match(). Michael Packer's answer does this using Oracle. However, MySQL lacks a built-in function for this; its regex functions only allow for saying if something matches, not extraction of the matches.

There might be a better solution specific to your use-case, depending on your data format. For example, if the year contains the only numbers in the column, etc.

Alternatively, you could do the processing in PHP. To allow future queries to do this, you could have PHP extract the year value from the text and store it in a separate column. SELECT queries could then GROUP BY that column.

Select one row without duplicate entries on regex based

1 Answers1