T-SQL字符索引

Question

是否有脚本可以提取列中的数据，将服务器名拆分到自己的列中？

| 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"}

所以ID1&2会变成下面的样子吗？我知道char&patindex可以帮助我理清思路。

| 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

您可以将存储的数据转换为有效的JSON数组，并使用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)

结果：

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