T-SQL Char index patindex

Question

Does anyone have script that can pull out data held in the columns to split out the servernames in to their own columns?

| ID|  Servers
|:-:|---------------------------------------------------------------------------------
| 1 |   {"Name":"SQL-Vlfflk43E"}
| 2 |   {"Name":"SQL-VgflkglkdA"},{"Name":"SQL-VCkfkjgitrE"},{"Name":"SQL-;bv;b;b"},{"Name":"SQL-kkkbdddb"}
| 3 |  {"Name":"SQL-VgkgkgA"},{"Name":"SQL-VfkgkjygtbB"},{"Name":"SQL-lglg"}
| 4 |   {"Name":"SQL-VotoevB"},{"Name":"SQL-VCfkjfkjrtrE"},{"Name":"SQL-lglkgl"}
| 5 |   {"Name":"SQL-VblgltotA"},{"Name":"SQL-VCfkfkgE"},{"Name":"SQL-lkgkjgkg"}
| 6 |   {"Name":"SQL-VCggkgkg"}

So ID 1 & 2 would become like the below? I know char & patindex can help just struggling to get my head around it.

| ID| Text                                                                                               | Server1       | Server2       | Server3   | Server4 
| 1 | {"Name":"SQL-Vlfflk43E"}                                                                           |SQL-Vlfflk43E  |null           |null       | null
| 2 | {"Name":"SQL-VgflkglkdA"},{"Name":"SQL-VCkfkjgitrE"},{"Name":"SQL-kkkbvb;b"},{"Name":"SQL-kkkbdddb"}|SQL-VgflkglkdA|SQL-VCkfkjgitrE|SQL-kkkbvb | SQL-kkkbdddb   

Answer 1

You may transform the stored data into a valid JSON array and parse it with JSON_VALUE():

SELECT 
   ID, 
   Server1 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[0].Name'),
   Server2 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[1].Name'),
   Server3 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[2].Name'),
   Server4 = JSON_VALUE(CONCAT('[', Servers, ']'), '$[3].Name')
FROM (VALUES
   (1, '{"Name":"SQL-Vlfflk43E"}'),
   (2, '{"Name":"SQL-VgflkglkdA"},{"Name":"SQL-VCkfkjgitrE"},{"Name":"SQL-;bv;b;b"},{"Name":"SQL-kkkbdddb"}'),
   (3, '{"Name":"SQL-VgkgkgA"},{"Name":"SQL-VfkgkjygtbB"},{"Name":"SQL-lglg"}'),
   (4, '{"Name":"SQL-VotoevB"},{"Name":"SQL-VCfkjfkjrtrE"},{"Name":"SQL-lglkgl"}'),
   (5, '{"Name":"SQL-VblgltotA"},{"Name":"SQL-VCfkfkgE"},{"Name":"SQL-lkgkjgkg"}'),
   (6, '{"Name":"SQL-VCggkgkg"}')
) v (ID, Servers)

Result:

ID  Server1        Server2          Server3     Server4
-------------------------------------------------------------
1   SQL-Vlfflk43E           
2   SQL-VgflkglkdA SQL-VCkfkjgitrE  SQL-;bv;b;b SQL-kkkbdddb
3   SQL-VgkgkgA    SQL-VfkgkjygtbB  SQL-lglg    
4   SQL-VotoevB    SQL-VCfkjfkjrtrE SQL-lglkgl  
5   SQL-VblgltotA  SQL-VCfkfkgE     SQL-lkgkjgkg    
6   SQL-VCggkgkg