Mysql – thesql apply regexp_replace for whole column/multiple rows

MySQL

I Have column exe_production inside users_acts table with data like `blah blah blah 10.10.2020 (any date) blah blah.

I need to order this column by date.

My steps:

Get date from string by next query:

SELECT REGEXP_REPLACE((SELECT exe_production FROM users_acts LIMIT 1), '[^[0-9]{2}[.]{1}[0-9]{2}[.]{1}[0-9]{4}]', '');

Convert all results strings to date by DATE_FORMAT statement.
Apply ORDER BY statement.

The problem is that I can't apply first query for multiple rows/values, only by LIMIT 1. How I can solve it?

Best Answer

Your idea was good, but you need STR_TO_DATE and you need the REGEXP_REPLACE in the SELECT not around a subselect

CREATE TABLE users_acts (exe_production varchar(100))

INSERT INTO users_acts VALUES ('blah blah blah 10.10.2020 (any date) blah blah')

SELECT 
    STR_TO_DATE(REGEXP_REPLACE(exe_production,
            '[^[0-9]{2}[.]{1}[0-9]{2}[.]{1}[0-9]{4}]',
            ''),'%d.%m. %Y')
FROM
    users_acts

| STR_TO_DATE(REGEXP_REPLACE(exe_production,
           '[^[0-9]{2}[.]{1}[0-9]{2}[.]{1}[0-9]{4}]',
           ''),'%d.%m. %Y') |
| :----------------------------------------------------------------------------------------------------------------------------- |
| 2020-10-10                                                                                                                     |

db<>fiddle here

Related Solutions

Mysql – Create multiple columns from a single column filtering criteria

I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, this is known as statistical queries and then this is the query I ended up using:

SELECT user_id as userid, count(id) Total,
COUNT(IF(revision = 'pending', 1, NULL)) as pending,
COUNT(IF(revision = 'approved', 1, NULL)) as approved,
COUNT(IF(revision = 'rejected', 1, NULL)) as rejected
FROM table1
GROUP BY user_id

This will fill those values by count only if criteria is met.

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT

SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
(
    SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
    FROM city A INNER JOIN citizen B
    ON A.id = B.city_id GROUP BY A.id,A.name
) AA;

Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/9f4b3/1

If the names become too long due to truncation, you will have to extend GROUP_CONCAT's maximum length. To set it to 16K, run the following:

SET group_concat_max_len = 1024 * 16;
SET GLOBAL group_concat_max_len = 1024 * 16;

First line is for the session, the second is for all new DB Connections.

Best Answer

Related Solutions

Mysql – Create multiple columns from a single column filtering criteria

Mysql – Selecting multiple columns from multiple rows in one column (subquery?)

Related Question