I was given the task to look into data in MySQL database. I was able to do some processing which allowed me to end up with columns that have the pattern of:

• “!!” for boundaries
• “##” to indicate separation
• “=” to indicate key-value pairs with the data preceding = being the key and the data after the = being the value. The keys-value pairs have been grouped already into ascending numerical order

Please see below:

| ID| Findings |
| 1 | !!45=2!! ## !!7395=2019-03-26!! ## !!7396=2019-03-27!!|
| 2 | !!21=16.5!! ## !!22=22!! ## !!22=22!! ## !!45=1!! ## !!309=fast breathing!! ## !!7395=2019-03-26!! ## !!7396=2019-03-26!! ## !!8937=asthma!! ## !!8937=hypertension!! ## !!8937=diabetes!!|

I would like to try and make it so that I can have a separate column for each key. For the example above, I want to make it like so:

| ID | 21 | 22 | 45 | 309 | 7395 | 7396 | 8937 |
| 1 | null | null | 2 | null | 2019-03-26 | 2019-03-27 | nulll |
| 2 | 16.5 | 22 | 1 | fast breathing | 2019-03-26 | 2019-03-26 | asthma,hypertension,diabetes |

Some additional information:

1. Not all keys are represented in each string. For example, ID one only has keys 45, 7395, and 7396. ID 2 has keys 21, 22, 45, 309, 7395, 7396, 8937
2. Some keys may repeat. For ID 2, key 22 has repeated twice, key 8937 has repeated thrice. For such instances, I am thinking of concatenating them and adding a delimeter “,”. I am open to suggestions for any other way of handling these 🙂
3. I’m not sure if the !! are actually helping. I think I can just replace all instances of “!!” to “” before any other REGEX if it can make the process of parsing these data easier.

This is actually new to me and well, frankly this set-up has made me hit a wall :)) any help would gladly be appreciated!

I tried to study about REGEX as I think that will help. However, I am not confident in my understanding of REGEX.