0

currently I have a tricky problem to solve, which I had previously realised in VBScript and I am now trying to transfer to a MySQL view. So far I have not succeeded in doing this. Who has a solution for the following task?

Table1

   Num Code                 Key CodeCommon CodeIndiv
     1 FE/MCG/90/K            1 FE/MCG      /90/K
     2 FE/MCG/150/5           1 FE/MCG      /150/5
     3 FE/MCG/200/26          1 FE/MCG      /200/26
     4 FE/MCGO67/340/15       1 FE/MCG      O67/340/15
     5 FE/MCG/600/315         1 FE/MCG      /600/315
     6 FE/MCG/345/17          1 FE/MCG      /345/17
     7 FE/MCGH1/90/K          1 FE/MCG      H1/90/K
     8 HE/MCGI3/150/5         1 FE/MCG      I3/150/5
     9 FE/MCGJ2/200/26        1 FE/MCG      J2/200/26
    10 FE/MCGK88/340/15       1 FE/MCG      K88/340/15
    11 FE/MCGL543/600/315     1 FE/MCG      L543/600/315
    12 FE/MCGM4653/345/17     1 FE/MCG      M4653/345/17
    13 AFFJ/MCG/90/K          2    
    14 AFFJ/MCG-150/5         2    
    15 AFFJ/MCG-200/26        2    
    16 AFFJ/MCGO67-340/15     2    
    17 AFFJ/MCG/600/315       2    
    18 AFFJ/MCG/345/17        2    
    19 AFFJ/MCGH1/90/K        2    
    20 AFFJ/MCGI3/150/5       2    
    21 AFFJ/MCGJ2/200/26      2    
    22 AFFJ/MCGK88/340/15     2    
    23 AFFJ/MCGL543/600/315   2    
    24 AFFJ/MCGM4653/345/17   2

From this table, in the [Code] column within a [Key], determine which first characters in all cells have common values and split the result into two cells [CodeCommon] and [CodeIndiv]. The number and type of common values are completely different (numbers, letters, characters).

I would be very happy and grateful if someone could help me. Many thanks in advance

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Ideefox
  • 1
  • 1
  • What task? What is the question? – Gordon Linoff Mar 16 '21 at 16:09
  • 1
    `8 HE/MCGI3/150/5 1 FE/MCG I3/150/5` eh? – Strawberry Mar 16 '21 at 16:14
  • What you're asking is essentially what geneticists have to do all day long, so I'd go ask them. They may well tell you that an RDBMS is not best suited to this kind of analysis. – Strawberry Mar 16 '21 at 16:16
  • This looks like an iterative / recursive process, probably not the best use of a RDBMS! – Stu Mar 16 '21 at 17:12
  • Why do 14, 15, 16 have hyphen instead of slash? – June7 Mar 16 '21 at 19:34
  • Does this answer your question? [How to split the name string in mysql?](https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql) – June7 Mar 16 '21 at 19:34
  • @Strawberry: Thank you very much for the first answer and for the table format. Yes, I know that this query is recursive and should be in external programs rather than in the database. But in this case, it is a limited number of "order codes" to be split per item group. I look forward to more answers. – Ideefox Mar 17 '21 at 08:00
  • Well, in this instance, the commonality is a simple string : 'MCG', so you can just split on that. – Strawberry Mar 17 '21 at 08:09
  • @Strawberry: No, that would be too simple: here is only an example; within a key, the cell code contains a wide variety of values – Ideefox Mar 17 '21 at 08:17
  • That's the best we can do with the sample data provided – Strawberry Mar 17 '21 at 08:21

0 Answers0