首页 > 解决方案 > 从文本字符串返回多个 GUID

问题描述

我已经快 2 年没有写过 SQL 查询了,所以我有点生疏了。我的数据库有一个文本字段,其中存储一些非结构化数据,如下所示:

{"title":"MyDashboardTitle","layoutValue":"50%50%_100%","layout":[{"columns":[{"dashletsIds":[0,1],"class":"column percents50 ui-sortable"},{"dashletsIds":[2,3],"class":"column percents50 ui-sortable"}]},{"columns":[{"dashletsIds":[4,5],"class":"column percents100 ui-sortable"}]}],"dashlets":[{"data":{"dataSourceId":"efe860e8-7e7f-414d-bc7d-
 86dacf0cb352","chartType":"mscolumn3d","labelsColumn":"Description","height":"350","captionModified":true,"chart":{"items":{"theme":"default","bgColor":"#ffffff","rowsToDisplay":"100","caption":"Top 10 Customers Potentially Impacted by Vulnerabilities","bgAlpha":"100","animation":"1","subCaption":"","numberPrefix":"","showLegend":"0","showValues":"1","showLabels":"1","xAxisName":"","yAxisName":""},"counter":13},"values":[{"aggregation":8,"value":"High Risk","toolText":""},{"aggregation":8,"value":"Medium Risk","toolText":""}],"colorRanges":
[{"valueFrom":0,"valueTo":25,"color":"#008000"},{"valueFrom":25,"valueTo":50,"color":"#ffff00"},{"valueFrom":50,"valueTo":75,"color":"#ffa500"},{"valueFrom":75,"valueTo":100,"color":"#ff0000"}],"colorPoints":[{"color":"#ffff00","label":"Low","value":0},
{"color":"#008000","label":"High","value":100000}]},"title":"","dashletClass":"chart"},{"data":{"dataSourceId":"e9716f7b-d2f7-4b69-b652-46a2a869663f","chartType":"mscolumn3d","labelsColumn":"Device Category","height":"350","captionModified":true,"chart":{"items":{"theme":"default","bgColor":"#ffffff","rowsToDisplay":"100","caption":"Vulnerabilities by Device 
Category","bgAlpha":"100","animation":"1","subCaption":"","numberPrefix":"","showLegend":"1","showValues":"1","showLabels":"1","xAxisName":"","yAxisName":""},"counter":13},"values":[{"aggregation":8,"value":"High Risk","toolText":""},{"aggregation":8,"value":"Medium Risk","toolText":""},{"aggregation":8,"value":"Low Risk","toolText":""}],"colorRanges":[{"valueFrom":0,"valueTo":25,"color":"#008000"},{"valueFrom":25,"valueTo":50,"color":"#ffff00"},{"valueFrom":50,"valueTo":75,"color":"#ffa500"},{"valueFrom":75,"valueTo":100,"color":"#ff0000"}],"colorPoints":[{"color":"#ffff00","label":"Low","value":0},
{"color":"#008000","label":"High","value":100000}]},"title":"","dashletClass":"chart"},{"data":{"dataSourceId":"786f8c08-98f7-435c-ae61-c15697f4d51b","chartType":"mscolumn3d","labelsColumn":"System Name","height":"350","captionModified":true,"chart":{"items":{"theme":"default","bgColor":"#ffffff","rowsToDisplay":"100","caption":"Vulnerabilities by 
System","bgAlpha":"100","animation":"1","subCaption":"","numberPrefix":"","showLegend":"1","showValues":"1","showLabels":"1","xAxisName":"","yAxisName":""},"counter":13},"values":[{"aggregation":8,"value":"High Risk Vulnerabilities","toolText":""},{"aggregation":8,"value":"Medium Risk Vulnerabilities","toolText":""}],"colorRanges":[{"valueFrom":0,"valueTo":25,"color":"#008000"},{"valueFrom":25,"valueTo":50,"color":"#ffff00"},{"valueFrom":50,"valueTo":75,"color":"#ffa500"},{"valueFrom":75,"valueTo":100,"color":"#ff0000"}],"colorPoints":[{"color":"#ffff00","label":"Low","value":0},
{"color":"#008000","label":"High","value":100000}]},"title":"","dashletClass":"chart"},{"data":{"dataSourceId":"344c64ab-27e3-4e73-b76b-15d08d6cde66","chartType":"msbar3d","labelsColumn":"Business Function","height":"350","captionModified":true,"chart":{"items":{"theme":"default","bgColor":"#ffffff","rowsToDisplay":"100","caption":"Business Function Vulnerability 
Impact","bgAlpha":"100","animation":"1","subCaption":"Ordered by Business Function Priority","numberPrefix":"","showLegend":"1","showValues":"0","showLabels":"1","xAxisName":"","yAxisName":""},"counter":13},"values":[{"aggregation":8,"value":"High Risk Count","toolText":""},{"aggregation":8,"value":"Medium Risk Count","toolText":""}],"colorRanges":[{"valueFrom":0,"valueTo":25,"color":"#008000"},{"valueFrom":25,"valueTo":50,"color":"#ffff00"},{"valueFrom":50,"valueTo":75,"color":"#ffa500"},{"valueFrom":75,"valueTo":100,"color":"#ff0000"}],"colorPoints":[{"color":"#ffff00","label":"Low","value":0},
{"color":"#008000","label":"High","value":100000}]},"title":"","dashletClass":"chart"},{"data":{"dataSourceId":"b2c30000-0000-0000-0000-000000000000","chartType":"pareto3d","labelsColumn":"Product","height":"350","captionModified":true,"chart":{"items":{"theme":"default","bgColor":"#ffffff","rowsToDisplay":"100","caption":"Software Vulnerabilities","bgAlpha":"100","animation":"1","subCaption":"by Software and Version","numberPrefix":"","showLegend":"0","showValues":"0","showLabels":"1","xAxisName":"","yAxisName":""},"counter":13},"values":[{"aggregation":8,"value":"count","toolText":""}],"colorRanges":[{"valueFrom":0,"valueTo":25,"color":"#008000"},{"valueFrom":25,"valueTo":50,"color":"#ffff00"},{"valueFrom":50,"valueTo":75,"color":"#ffa500"},{"valueFrom":75,"valueTo":100,"color":"#ff0000"}],"colorPoints":[{"color":"#ffff00","label":"Low","value":0},{"color":"#008000","label":"High","value":100000}]},"title":"","dashletClass":"chart"},{"data":{"dataSourceId":"58346380-8bee-4838-91a4-80400352031a","labelsColumn":"label","height":"","captionModified":false,"chart":{"items":
{"height":"","rowsToDisplay":"1000","caption":null,"animation":null,"bgColor":"#ffffff","showAllColumns":"0"},"counter":6},"values":[{"value":"System Name","toolText":""},{"value":"Asset Name","toolText":""},{"value":"CVEScore","toolText":""},{"value":"MfgName","toolText":""},{"value":"ProdName","toolText":""},{"value":"Version","toolText":""},{"value":"Summary","toolText":""}]},"title":"Vulnerabiliites List","dashletClass":"table"}],"filters":[]}

虽然数据有一些格式,但它不是 CSV 或任何高度结构化的东西,每个字段都有独特的格式。

我正在尝试返回所有datasourceID的。我的猜测是我需要使用递归函数并搜索 '<code>datasourceID:' String,然后获取接下来的 36 个字符。我可以弄清楚如何在第一次出现时执行此操作,但无法弄清楚如何搜索下一次出现。

任何提示表示赞赏!

标签: sqlsql-server

解决方案


推荐阅读