我在表中有一個 nvarchar 列，它將資料存盤為 '{CRLF}' 分隔值。這些值可以采用“namespace#name$value”格式，也可以采用其他格式。
我需要提取分離的命名空間、名稱和值并跳過其他格式的資料。我可以通過查詢來做到這一點：
select
SUBSTRING([value], 1, CHARINDEX('#',[value]) - 1) as paramNamespace,
SUBSTRING([value], CHARINDEX('#',[value]) 1, CHARINDEX('$',[value]) - CHARINDEX('#',[value]) - 1) as paramName,
SUBSTRING([value], CHARINDEX('$',[value]) 1, LEN([value])) as paramValue
FROM STRING_SPLIT(REPLACE((select data from test where Id = 1), '{CRLF}', CHAR(7)), CHAR(7))
WHERE [value] LIKE '%#%$%'
但是，當我還嘗試使用此查詢按 paramName 進行過濾時：
select * from (select
SUBSTRING([value], 1, CHARINDEX('#',[value]) - 1) as paramNamespace,
SUBSTRING([value], CHARINDEX('#',[value]) 1, CHARINDEX('$',[value]) - CHARINDEX('#',[value]) - 1) as paramName,
SUBSTRING([value], CHARINDEX('$',[value]) 1, LEN([value])) as paramValue
FROM STRING_SPLIT(REPLACE((select data from test where Id = 1), '{CRLF}', CHAR(7)), CHAR(7))
WHERE [value] LIKE '%#%$%') a
where paramName IN ('name', 'name3')
它回傳
傳遞給 LEFT 或 SUBSTRING 函式的長度引數無效。
看起來“WHERE [value] LIKE '%#%$%'”沒有首先執行，它嘗試以其他格式對值進行子串。
我應該如何撰寫此查詢以按 paramName 過濾它？
架構：
CREATE TABLE test (Id INT, data nvarchar(max));
INSERT INTO test VALUES (1, N'namespace#name$value{CRLF}somedatainotherformat{CRLF}namespace2#name2$value2{CRLF}namespace3#name3$value3{CRLF}');
小提琴：http ://sqlfiddle.com/#!18/e5d71/1
uj5u.com熱心網友回復：
也許PARSENAME將資料拆分為列是您所追求的：
CREATE TABLE test (Id int,
data nvarchar(MAX));
INSERT INTO test
VALUES (1, N'namespace#name$value{CRLF}somedatainotherformat{CRLF}namespace2#name2$value2{CRLF}namespace3#name3$value3{CRLF}');
GO
SELECT *
FROM dbo.test t
CROSS APPLY STRING_SPLIT(REPLACE(t.data,'{CRLF}','|'),'|') SS
CROSS APPLY (VALUES(PARSENAME(TRANSLATE(SS.[value],'$#','..'),3),PARSENAME(TRANSLATE(SS.[value],'$#','..'),2),PARSENAME(TRANSLATE(SS.[value],'$#','..'),1)))V(paramNamespace, paramName, paramValue)
WHERE V.paramName IN ('name', 'name3')
GO
DROP TABLE test;
uj5u.com熱心網友回復：
這是一種選擇：
Select paramNamespace = substring(v.data, 1, p1.pos - 2)
, paramName = substring(v.data, p1.pos, p2.pos - p1.pos - 1)
, paramValue = substring(v.data, p2.pos, p3.pos - p2.pos - 1)
From test As t
Cross Apply string_split(replace(t.data, '{CRLF}', '|'), '|') As s
Cross Apply (Values (concat(s.value, '#$'))) As v(data)
Cross Apply (Values (charindex('#', v.data, 1) 1)) As p1(pos)
Cross Apply (Values (charindex('$', v.data, p1.pos) 1)) As p2(pos)
Cross Apply (Values (charindex('#', v.data, p2.pos) 1)) As p3(pos) --end of set
Where p2.pos - p1.pos > 1;
我們將分隔符連接到末尾 - 然后過濾掉分隔符之間的差異大于 1 的任何行。因此，任何只有 '#$' 的行或以 '#$' 開頭的行或有其他一些行的行不包含任何一個分隔符的值。
如果您的資料可以有任何一個分隔符 - 但不是另一個，則需要以其他方式過濾掉它們。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/311535.html
標籤：sql sql-server 查询语句 sql-server-2017
上一篇：統計SQL中連續結果的數量