Create stored procedure or function for multiple replace function using Mysql

user454973 0
Viewed 40

I need to replace the value from old format to new format as shown below. I have tried using multiple replace function, it works but not a smart solution. Is it possible to create a function or Store Procedure (SP) in an efficient way? I need to update the table at once.

Old Format (possible scenarios): i) Preferred Time: Monday (8-20), Tuesday (8-12), Wednesday (8-12), Saturday (8-12), Other Type Of Contact: SMS, EMAIL

ii) Preferred Time: Monday (8-12,17-20), Tuesday (8-17), Wednesday (8-12), Thursday (8-20)

New Format: i) MON08-12, MON12-17, MON17-20, TUE08-12, WED08-12, SAT08-12, SMS, EMAIL

ii) MON08-12, MON17-20, TUE08-12, TUE12-17, WED08-12, THU08-12, THU12-17, THU17-20

  • Note: Saturday has only one combination (08-12), whereas the rest of the weekdays from MON-FRI can have any combinations: (8-12) or (12-17) or (17-20) or (8-12,17-20) or (8-20) or (8-17) or (12-20)

Points to include when creating the function/SP:

  • Trim the leading space in the column value
  • Remove the string 'Preferred Time' and 'Other Type Of Contact'
  • Replace '8' with '08'
  • Replace (8-20) with 08-12, 12-17, 17-20
  • Replace (8-17) with 08-12, 12-17
  • Replace (12-20) with 12-17, 17-20

Hope someone can help me. Thanks in advance.

mysql
1 Answers
Step 1 - replace 'Preferred Time' and 'Other Type Of Contact' with comma (2 REPLACE)
Step 2 - remove all spaces, semicolons and brackets (1 REGEXP_REPLACE)
Step 3 - parse the whole value to separate values (1 REPLACE, 1 CONCAT and 1 JSON_TABLE)
Step 4 - divide single value to DOW and timerange (2 REGEXP_SUBSTRING)
Step 5 - divide timerange if needed (1 CASE)
Step 6 - reconstruct value (1 CONCAT and 1 GROUP_CONCAT)

This everything should be performed in temporary table.

Akina0
Related