I know that this question had been asked a lot by I can not find suitable solution for my case.
我知道这个问题已经被问了很多,我找不到合适的解决方案。
I have variations of a string:
我有弦的变体:
valA|valB|1|val1a|Val1b|Val2a|Val2b|Val3a|Val3b
valA | valB | 1 | val1a | Val1b | Val2a | Val2b | Val3a | Val3b
Where 3rd element is between 0 and 3 and shows how many ValNa
and ValNb
will be after it. When it is 0 then no elements after it. If 1 then only 1 couple of elements will be after it (Val1a
and Val1b
).
第3个元素在0到3之间,显示在它之后有多少缬氨酸和缬氨酸。当它是0之后就没有元素了。如果只有1个元素(Val1a和Val1b)。
I need TSQL select that return each element as column. For example:
我需要TSQL select返回每个元素作为列。例如:
SELECT valA, valB, 1, val1a, Val1b
选择valA, valB, 1, val1a, Val1b
I can not use PARSENAME
because it allows 4 elements max.
我不能使用PARSENAME,因为它最多允许4个元素。
Here are examples of all my combinations:
以下是我所有组合的例子:
valA|valB|0
valA | valB | 0
valA|valB|1|val1a|Val1b
valA | valB | 1 | val1a | Val1b
valA|valB|2|val1a|Val1b|Val2a|Val2b
valA | valB | 2 | val1a | Val1b | Val2a | Val2b
valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b
valA | valB | 3 | val1a | Val1b | Val2a | Val2b | Val3a | Val3b
Rigth now I am using this solution but I can not manage final element:
现在我正在使用这个解决方案,但是我不能管理最终元素:
DECLARE @param AS VARCHAR(MAX) = 'valA|ValB|2|val1a|val1b|val2a|val2b';
DECLARE @delimiter AS CHAR(1) = '|';
SELECT a FROM (VALUES (@param + @delimiter)) AS MyTable(a);
SELECT
CASE WHEN P1.Pos>0 THEN LEFT(Prm,P1.Pos-1) ELSE '' END AS valA,
CASE WHEN P2.Pos>0 THEN SUBSTRING (Prm, P1.Pos + 1, P2.Pos - P1.Pos - 1) ELSE '' END AS valB,
CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END AS Num,
CASE WHEN (P4.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 1) THEN SUBSTRING (Prm, P3.Pos + 1, P4.Pos - P3.Pos - 1) ELSE '' END AS val1a,
CASE WHEN (P5.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 1) THEN SUBSTRING (Prm, P4.Pos + 1, P5.Pos - P4.Pos - 1) ELSE '' END AS val1b,
CASE WHEN (P6.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 2) THEN SUBSTRING (Prm, P5.Pos + 1, P6.Pos - P5.Pos - 1) ELSE '' END AS val2a,
CASE WHEN (P7.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END >= 2) THEN SUBSTRING (Prm, P6.Pos + 1, P7.Pos - P6.Pos - 1) ELSE '' END AS val2b,
CASE WHEN (P8.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END = 3) THEN SUBSTRING (Prm, P7.Pos + 1, P8.Pos - P7.Pos - 1) ELSE '' END AS val3a,
CASE WHEN (P9.Pos>0) AND (CASE WHEN P3.Pos>0 THEN SUBSTRING (Prm, P2.Pos + 1, P3.Pos - P2.Pos - 1) ELSE 0 END = 3) THEN SUBSTRING (Prm, P8.Pos + 1, P9.Pos - P8.Pos - 1) ELSE '' END AS val3b
FROM
(VALUES (@param + @delimiter)) AS baseTable(Prm)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param))) as P1(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P1.Pos+1))) as P2(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P2.Pos+1))) as P3(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P3.Pos+1))) as P4(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P4.Pos+1))) as P5(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P5.Pos+1))) as P6(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P6.Pos+1))) as P7(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P7.Pos+1))) as P8(Pos)
CROSS APPLY (SELECT (CHARINDEX(@delimiter, @param, P8.Pos+1))) as P9(Pos)
EDIT:
编辑:
I need a kind of bullet-proof solution so even my string is empty the SQL just return empty values.
我需要一种防弹的解决方案,这样即使我的字符串是空的SQL也会返回空值。
Please, who vote down for my question, can you just put comment why? So I can take a note and not make the same mystake again.
谁对我的问题投了反对票,你能不能说一下为什么?所以我可以记下来,不再做同样的事。
1 个解决方案
#1
4
Test Data
DECLARE @t TABLE (Value VARCHAR(8000))
INSERT INTO @t VALUES
('valA|valB|0'),
('valA|valB|1|val1a|Val1b'),
('valA|valB|2|val1a|Val1b|Val2a|Val2b'),
('valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b')
Query
;WITH Split_Fields (Field, xmlfields)
AS
(
SELECT Value AS Field,
CONVERT(XML,'<Fields><field>'
+ REPLACE(Value,'|', '</field><field>') + '</field></Fields>') AS xmlfields
FROM @t
)
SELECT Field
,xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1
,xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2
,xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3
,xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4
,xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
,xmlfields.value('/Fields[1]/field[6]','varchar(100)') AS Field6
,xmlfields.value('/Fields[1]/field[7]','varchar(100)') AS Field7
,xmlfields.value('/Fields[1]/field[8]','varchar(100)') AS Field8
,xmlfields.value('/Fields[1]/field[9]','varchar(100)') AS Field9
,xmlfields.value('/Fields[1]/field[10]','varchar(100)') AS Field10
FROM Split_Fields
Result
╔═════════════════════════════════════════════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦═════════╗
║ Field ║ Field1 ║ Field2 ║ Field3 ║ Field4 ║ Field5 ║ Field6 ║ Field7 ║ Field8 ║ Field9 ║ Field10 ║
╠═════════════════════════════════════════════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬═════════╣
║ valA|valB|0 ║ valA ║ valB ║ 0 ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║
║ valA|valB|1|val1a|Val1b ║ valA ║ valB ║ 1 ║ val1a ║ Val1b ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║
║ valA|valB|2|val1a|Val1b|Val2a|Val2b ║ valA ║ valB ║ 2 ║ val1a ║ Val1b ║ Val2a ║ Val2b ║ NULL ║ NULL ║ NULL ║
║ valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b ║ valA ║ valB ║ 3 ║ val1a ║ Val1b ║ Val2a ║ Val2b ║ Val3a ║ Val3b ║ NULL ║
╚═════════════════════════════════════════════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩═════════╝
#1
4
Test Data
DECLARE @t TABLE (Value VARCHAR(8000))
INSERT INTO @t VALUES
('valA|valB|0'),
('valA|valB|1|val1a|Val1b'),
('valA|valB|2|val1a|Val1b|Val2a|Val2b'),
('valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b')
Query
;WITH Split_Fields (Field, xmlfields)
AS
(
SELECT Value AS Field,
CONVERT(XML,'<Fields><field>'
+ REPLACE(Value,'|', '</field><field>') + '</field></Fields>') AS xmlfields
FROM @t
)
SELECT Field
,xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1
,xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2
,xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3
,xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4
,xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
,xmlfields.value('/Fields[1]/field[6]','varchar(100)') AS Field6
,xmlfields.value('/Fields[1]/field[7]','varchar(100)') AS Field7
,xmlfields.value('/Fields[1]/field[8]','varchar(100)') AS Field8
,xmlfields.value('/Fields[1]/field[9]','varchar(100)') AS Field9
,xmlfields.value('/Fields[1]/field[10]','varchar(100)') AS Field10
FROM Split_Fields
Result
╔═════════════════════════════════════════════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╦═════════╗
║ Field ║ Field1 ║ Field2 ║ Field3 ║ Field4 ║ Field5 ║ Field6 ║ Field7 ║ Field8 ║ Field9 ║ Field10 ║
╠═════════════════════════════════════════════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬════════╬═════════╣
║ valA|valB|0 ║ valA ║ valB ║ 0 ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║
║ valA|valB|1|val1a|Val1b ║ valA ║ valB ║ 1 ║ val1a ║ Val1b ║ NULL ║ NULL ║ NULL ║ NULL ║ NULL ║
║ valA|valB|2|val1a|Val1b|Val2a|Val2b ║ valA ║ valB ║ 2 ║ val1a ║ Val1b ║ Val2a ║ Val2b ║ NULL ║ NULL ║ NULL ║
║ valA|valB|3|val1a|Val1b|Val2a|Val2b|Val3a|Val3b ║ valA ║ valB ║ 3 ║ val1a ║ Val1b ║ Val2a ║ Val2b ║ Val3a ║ Val3b ║ NULL ║
╚═════════════════════════════════════════════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩════════╩═════════╝