Extracting string values from a column into different columns

Question

I wish to extract string values from a column in a table into different columns, the challenge with this scenario is that the order of arrangement of the string values from one cell to another is not consistent.

Example of the values in the column as it appears in different rows/cells:

{"Op":"U","guid":1921162,"tags":"client:HCP2,client:CVI2 Family or Friend Supported,client:Dee Green,"}
{"Op":"U","guid":632720,"tags":"client:Marija Mustac,client:CVI2 Family or Friend Supported,"}
{"Op":"U","guid":1552284,"tags":"client:CVI2 Family or Friend Supported,client:CHSP,client:Carol Chong,"}

Using the first row as an example, the values I want to extract are the "HCP2", "CVI2 Family or Friend Supported", "Dee Green". Looking at the second row above, you would notice the order of the values I want to extract is not same as in row 1 and row 3. However, I want the values to be extracted into 3 columns with a consistent order.

The 3 columns would be Name, Program Type, CVI Level. Using the above 3 row data as an example:

• The Name column should contain all the names extracted from the main column: "Dee Green", "Marija Mustac", "Carol Chong".
• The Program Type column should contain "HCP2", NULL (because the value was not specified), "CHSP".
• The CVI Level column should contain "CVI2", "CVI2", "CVI2".

There are basically 5 - 6 program types and 3 CVI levels.

I wish to accomplish the above in SQL (SQL Server)

I was able to extract the values into 3 columns, but the values in the columns were mixed up: some CVI values were found in the Name column, and some names were found in the Program Type column and vice versa.