需要帮助提取中间字符串

Question

在galons_column中，我有时会遇到像"98.0,2.45,0.25'"这样的字符串。我已经能够提取98.0和0.25，但中间的字符串我遇到了困难。我一直在尝试使用子字符串来提取中间的字符串，但我无法计算出其余的语法。

如有任何帮助或建议，我们将不胜感激。谢谢😊

我正在使用SSM 18。我使用下面的代码提取98.0和0.25

volume_sold_liters=LEFT(volume_sold_gallons,CHARINDEX(',',volume_sold_gallons)-1),
volume_sold_gallons = RIGHT(volume_sold_gallons,CHARINDEX(',', (REVERSE(volume_sold_gallons)))-1)
FROM project
WHERE store_number =3621
```````
--As for the middle string I was trying to use the code below with no success.
`````````
SELECT volume_sold_liters = SubString(volume_sold_gallons,CHARINDEX(',',volume_sold_gallons)+1),
RIGHT(volume_sold_gallons,CHARINDEX(',', (REVERSE(volume_sold_gallons)))-1) FROM project

Answer 1

使用一点JSON，将字符串解析为列是一件小事

模式非常清晰，因此您可以根据需要进行扩展。

示例

Declare @YourTable Table ([gallons] varchar(50))
Insert Into @YourTable Values 
('98.0,2.45,0.25')

Select A.* 
,Pos1  = JSON_VALUE(S,'$[0]')
,Pos2  = JSON_VALUE(S,'$[1]')
,Pos3  = JSON_VALUE(S,'$[2]')
From @YourTable A
Cross Apply ( values ( '["'+replace([gallons],',','","')+'"]' ) ) B(S)

结果

gallons         Pos1    Pos2    Pos3
98.0,2.45,0.25  98.0    2.45    0.25