我有一個存盤在 SQL Server 資料庫中的主機資訊表，該表有一個文本列，存盤格式類似于 Ansible 清單的字串。有關文本列中的示例專案，請參閱下面的文本。
host-001.servers.company.com desc='Production Web Cache' env='Prod' patch_round='Beta' dc='Main' rhel_v='7.6' primary='[email protected]' secondary='[email protected]'
我需要從文本列中提取某些屬性，例如 extract desc='Production Web Cache'，并獲取其 value Production Web Cache。我想在 SQL 查詢中使用正則運算式，希望得到一些指點。
或者，如果您知道實作此目的的另一種方法，我也會非常感謝您的提示。如果您需要更多說明，請告訴我。
uj5u.com熱心網友回復：
與約翰非常相似的方法。我首先使用 JSON 拆分器將資料分成幾部分，盡管這會將值放在下一個標頭中。我CHARINDEX用來找到值的結尾，然后使用那個LEFT/STUFF將 2 個值放入它們各自的部分。然后我LAG用來獲取實際的標題，而不是下一個值標題。最后，我洗掉了周圍的引號。
這是基于我的評論中的假設：
- 域位于開頭，后跟一個空格。
- 值不能包含 =。
- 所有值都用單引號 (') 括起來，所有名稱都不是
注意我沒有在結果中包含域，但是 SQL 應該足以讓您弄清楚如何添加它：
DECLARE @YourString nvarchar(4000) = N'host-001.servers.company.com desc=''Production Web Cache'' env=''Prod'' patch_round=''Beta'' dc=''Main'' rhel_v=''7.6'' primary=''[email protected]'' secondary=''[email protected]''';
WITH CTE AS(
SELECT *,
LAG(ContentHeader) OVER (ORDER BY [Key]) AS ActualHeader
FROM (VALUES(@YourString))V(YourString)
CROSS APPLY(VALUES(STUFF(@YourString, 1, CHARINDEX(N' ',@YourString),N'')))S(NewString)
CROSS APPLY OPENJSON('["' REPLACE(NewString,'=','","') '"]')OJ
CROSS APPLY(VALUES(NULLIF(CHARINDEX('''',OJ.[value],2),0)))CI(I)
CROSS APPLY(VALUES(LEFT(OJ.[Value],CI.I),STUFF(OJ.[Value],1,ISNULL(CI.I 1,0),'')))P(ContentValue,ContentHeader))
SELECT ActualHeader AS Header,
REPLACE([ContentValue],'''','') AS [Value]
FROM CTE
WHERE ActualHeader IS NOT NULL;
資料庫<>小提琴
uj5u.com熱心網友回復：
有點難看，但使用了一些 JSON（對序列進行 GTD）和視窗函式 lead() over()
例子
Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'host-001.servers.company.com desc=''Production Web Cache'' env=''Prod'' patch_round=''Beta'' dc=''Main'' rhel_v=''7.6'' primary=''[email protected]'' secondary=''[email protected]''')
Select A.ID
,Host = left(SomeCol,charindex(' ',SomeCol ' '))
,B.*
From @YourTable A
Cross Apply (
Select Item = ltrim(rtrim(right(Value,charindex(' ',reverse(Value) ' '))))
,Value = ltrim(rtrim(replace(
IsNull(lead( left(Value,nullif(len(Value) 1-charindex(' ',reverse(Value) ' '),0)),1) over (order by [Key])
,lead(right(Value,charindex(' ',reverse(Value) ' ')),1) over (order by [key])
),'''','')))
From OpenJSON( '["' replace(string_escape(SomeCol,'json'),'=','","') '"]' )
) B
Where B.Value is not null
結果
ID Host Item Value
1 host-001.servers.company.com desc Production Web Cache
1 host-001.servers.company.com env Prod
1 host-001.servers.company.com patch_round Beta
1 host-001.servers.company.com dc Main
1 host-001.servers.company.com rhel_v 7.6
1 host-001.servers.company.com primary admin@company.com
1 host-001.servers.company.com secondary manager@company.com
編輯 - 注入“HOST=”
Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'host-001.servers.company.com desc=''Production Web Cache'' env=''Prod'' patch_round=''Beta'' dc=''Main'' rhel_v=''7.6'' primary=''[email protected]'' secondary=''[email protected]''')
Select A.ID
,B.*
From @YourTable A
Cross Apply (
Select Item = ltrim(rtrim(right(Value,charindex(' ',reverse(Value) ' '))))
,Value = ltrim(rtrim(replace(
IsNull(lead(left(Value,nullif(len(Value) 1-charindex(' ',reverse(Value) ' '),0)),1) over (order by [Key])
,lead(right(Value,charindex(' ',reverse(Value) ' ')),1) over (order by [key])
),'''','')))
From OpenJSON( '["' replace(string_escape('host=' SomeCol,'json'),'=','","') '"]' )
) B
Where B.Value is not null
結果
ID Item Value
1 host host-001.servers.company.com
1 desc Production Web Cache
1 env Prod
1 patch_round Beta
1 dc Main
1 rhel_v 7.6
1 primary admin@company.com
1 secondary manager@company.com
uj5u.com熱心網友回復：
理想情況下，您的資料應存盤在單獨的列中。但是如果你要把它塞進一列，至少使用一種公認的格式，比如 XML 或 JSON。
鑒于單引號是有效的 XML 屬性定界符，您可以將其轉換為 XML 并使用 XQuery。
它不漂亮，因為主機名值沒有分隔
SELECT
v3.n.value('@host','varchar(255)'),
v3.n.value('@desc','varchar(1000)')
FROM t
CROSS APPLY(VALUES(
CHARINDEX(' ', t.value)
)) v1(space)
CROSS APPLY(VALUES(
CAST(
'<x host='''
CASE WHEN v1.space = 0
THEN t.value
ELSE LEFT(t.value, v1.space - 1) '''' SUBSTRING(t.value, v1.space, LEN(t.value))
END
' />'
AS xml)
)) v2(xml)
CROSS APPLY v2.xml.nodes('x') v3(n);
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/380126.html
標籤：sql sql-server 查询语句