Mysql - How do I use LEFT function in comma separated string value

paranthamanE
April 5, 2024
67 views
0 votes
2 Answers

The app_recurrenceexception column have deleted appointment date.

I achieved this way

SELECT
    app_recurrenceexception,
    (SELECT GROUP_CONCAT(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(app_recurrenceexception, ',', numbers.n), ',', -1), 8) SEPARATOR ',')
    FROM
    (
        SELECT 1 + a.N + b.N * 10 + c.N * 100 AS n
        FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
          UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
          UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
          UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
          UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 
          UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
          UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) AS numbers
    WHERE
        numbers.n <= 1 + (LENGTH(app_recurrenceexception) - LENGTH(REPLACE(app_recurrenceexception, ',', '')))
    ) AS modified_date
FROM
    `bm_appointment`
WHERE
    app_eventid = 15;

But I Want simple way of output

SELECT (20240402T073000Z,20240405T073000Z,20240408T073000Z,20240411T073000Z) from bm_appointment

how to split the first 8 words for each comma separated value in MSQL query. How to Extract the date from the above string.

Expected output is
20240402,20240405,20240408,20240411……

Tags: mysql

Answers

There many questions and answer about how split comma separated values in MySql (STRING_SPLIT).
See example with recursion.

Test data

create table bm_appointment(app_eventid int,app_recurrenceexception varchar(1000));
insert  into bm_appointment values
  (15,'20240402T073000Z,20240405T073000Z,20240408T073000Z,20240411T073000Z')
;

Query

with recursive r as(
  select 1 n,app_eventid
    ,case when instr(app_recurrenceexception,',')>0 then
         left(app_recurrenceexception,instr(app_recurrenceexception,',')-1)
     else app_recurrenceexception
     end modified_date
    ,case when instr(app_recurrenceexception,',')>0 then
         mid(app_recurrenceexception,instr(app_recurrenceexception,',')+1)
     else ''
     end rest
  from bm_appointment
  union all
  select n+1 n,app_eventid
    ,case when instr(rest,',')>0 then
         left(rest,instr(rest,',')-1)
     else rest
     end modified_date
    ,case when instr(rest,',')>0 then
         mid(rest,instr(rest,',')+1)
     else ''
     end rest
  from r
  where length(rest)>0
)
-- select app_eventid,n,modified_date 
-- from r;
select app_eventid
  ,group_concat(left(modified_date,8) order by modified_date separator ',') dates 
from r
group by app_eventid;

Output is

app_eventid	n	modified_date
15	1	20240402T073000Z
15	2	20240405T073000Z
15	3	20240408T073000Z
15	4	20240411T073000Z

After grouping (concatenation)

app_eventid	dates
15	20240401,20240402,20240405,20240408

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+

mysql> set @s = '20240402T073000Z,20240405T073000Z,20240408T073000Z,20240411T073000Z';

mysql> select regexp_replace(@s, 'T[[:digit:]]{6}Z', '') as dates;
+-------------------------------------+
| dates                               |
+-------------------------------------+
| 20240402,20240405,20240408,20240411 |
+-------------------------------------+

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – How do I use LEFT function in comma separated string value

Answers