英文:
Parse a long text string of the ini file stored in a cell of the SQL Server table
问题
Here's the translation of the text you provided:
帮助我解析存储在 SQL Server 表格单元格中的 INI 文件的长文本字符串。
我有一个包含列 id, rawINI 的表格。
在 rawINI 列中，INI 文件的结构如下所示：
[BlockName1]
Key1=value
Key2=value1,value2,value3
Key3=
[BlockName1]
Key1=value
Key2=value1,value2,value3
Key3=
需要将这个文本转换成新表格的形式，例如（也许你可以推荐另一种方式）：
Block,Key,Value
--------------------------------------
BlockName1,Key1,value
BlockName1,Key2,value1,value2,value3
BlockName1,Key3
BlockName2,Key1,value
BlockName2,Key2,value1,value2,value3
BlockName2,Key3
我不熟悉 T-SQL，也不知道从何处开始解决这个问题。如何解决这个问题？有哪些功能可以帮助？
英文:
Help me parse a long text string of the ini file stored in a cell of the SQL Server table.
I have a table with columns id, rawINI.
In the rawINI column, the structure of the INI file is as follows:
[BlockName1]
Key1=value
Key2=value1,value2,value3
Key3=
[BlockName1]
Key1=value
Key2=value1,value2,value3
Key3=
It is necessary to convert this text into the form of a new table, for example (maybe you can recommend another way?):
Block,Key,Value
--------------------------------------
BlockName1,Key1,value
BlockName1,Key2,value1,value2,value3
BlockName1,Key3
BlockName2,Key1,value
BlockName2,Key2,value1,value2,value3
BlockName2,Key3
I'm not familiar with T-SQL and I don't even know where to start solving this problem. How can this be solved? What features can help?
答案1
得分: 1
以下是您要求的代码部分的中文翻译：
作为示例：
DECLARE @TEXT VARCHAR(max) =
' [BlockName1]
Key1=value
Key2=value1,value2,value3
Key3=
[BlockName2]
Key1=value
Key2=value1,value2,value3
Key3=';
WITH
T AS
(
SELECT CONCAT('[', value) AS VAL, ROW_NUMBER() OVER(ORDER BY value) AS ORD
FROM STRING_SPLIT(@TEXT, '[')
WHERE value <> ''
),
S AS
(
SELECT ORD, LEFT(VAL, CHARINDEX(']', VAL)) AS BLOC,
SUBSTRING(VAL, CHARINDEX(']', VAL) + 3 , LEN(VAL) - CHARINDEX(']', VAL)) AS K
FROM T
)
SELECT ORD, BLOC, value AS CLE
FROM S
CROSS APPLY STRING_SPLIT(K, CHAR(10))
WHERE value <> '';
这是您要的翻译。如果您需要进一步的帮助，请告诉我。
英文:
As an example :
DECLARE @TEXT VARCHAR(max) =
'[BlockName1]
Key1=value
Key2=value1,value2,value3
Key3=
[BlockName2]
Key1=value
Key2=value1,value2,value3
Key3=';
WITH
T AS
(
SELECT CONCAT('[', value) AS VAL, ROW_NUMBER() OVER(ORDER BY value) AS ORD
FROM STRING_SPLIT(@TEXT, '[')
WHERE value <> ''
),
S AS
(
SELECT ORD, LEFT(VAL, CHARINDEX(']', VAL)) AS BLOC,
SUBSTRING(VAL, CHARINDEX(']', VAL) + 3 , LEN(VAL) - CHARINDEX(']', VAL)) AS K
FROM T
)
SELECT ORD, BLOC, value AS CLE
FROM S
CROSS APPLY STRING_SPLIT(K, CHAR(10))
WHERE value <> ''
That results :
ORD BLOC CLE
-------------------- ---------------------- ---------------------------
1 [BlockName1] Key1=value
1 [BlockName1] Key2=value1,value2,value3
1 [BlockName1] Key3=
2 [BlockName2] Key1=value
2 [BlockName2] Key2=value1,value2,value3
2 [BlockName2] Key3=
Thenyou can apply the same principe of STRING SPLIT (on coma) another time to extract values of keys
答案2
得分: 0
感谢@SQLPro的巨大帮助！我的问题的最终解决方案：
WITH
[BLOCKS] AS
(
SELECT CONCAT(' [', value) AS [RAW]
FROM STRING_SPLIT(@rawINI, '[')
WHERE value <> ''
),
[PAIRS] AS
(
SELECT LEFT([RAW], CHARINDEX(']', [RAW])) AS [BLOCK],
SUBSTRING([RAW], CHARINDEX(']', [RAW]) + 3 , LEN([RAW]) - CHARINDEX(']', [RAW])) AS [PAIR]
FROM [BLOCKS]
)
SELECT LTRIM(RTRIM([BLOCK])) AS [BLOCK],
LTRIM(RTRIM(REPLACE(LEFT([KeyVal], CHARINDEX('=',[KeyVal])), '=', ''))) AS [KEY],
LTRIM(RTRIM(REPLACE(SUBSTRING([KeyVal], CHARINDEX('=',[KeyVal]), LEN([KeyVal]) - CHARINDEX('=', [KeyVal])), '=', ''))) AS [VAL]
FROM
(
SELECT [BLOCK], value AS [KeyVal]
FROM [PAIRS]
CROSS APPLY STRING_SPLIT([PAIR], CHAR(10))
WHERE value <> ''
) AS RAWTABLE
WHERE [KeyVal] <> ''
p.s. 我需要更多了解CROSS APPLY...
英文:
Thank you so much for @SQLPro great help! The final solution to my problem:
WITH
[BLOCKS] AS
(
SELECT CONCAT('[', value) AS [RAW]
FROM STRING_SPLIT(@rawINI, '[')
WHERE value <> ''
),
[PAIRS] AS
(
SELECT LEFT([RAW], CHARINDEX(']', [RAW])) AS [BLOCK],
SUBSTRING([RAW], CHARINDEX(']', [RAW]) + 3 , LEN([RAW]) - CHARINDEX(']', [RAW])) AS [PAIR]
FROM [BLOCKS]
)
SELECT LTRIM(RTRIM([BLOCK])) AS [BLOCK],
LTRIM(RTRIM(REPLACE(LEFT([KeyVal], CHARINDEX('=',[KeyVal])),'=',''))) AS [KEY],
LTRIM(RTRIM(REPLACE(SUBSTRING([KeyVal], CHARINDEX('=',[KeyVal]), LEN([KeyVal]) - CHARINDEX('=', [KeyVal])),'=',''))) AS [VAL]
FROM
(
SELECT [BLOCK], value AS [KeyVal]
FROM [PAIRS]
CROSS APPLY STRING_SPLIT([PAIR], CHAR(10))
WHERE value <> ''
) AS RAWTABLE
WHERE [KeyVal] <> ''
p.s. i need to learn more about CROSS APPLY ...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论