是否可以在SQL Server中直接将浮点值与字符串隔离？
我尝试了Extract float from String/Text SQL Server的解决方案，但无济于事。非常感谢！
SELECT strCondition,Amount1,Amount2,Amount3
FROM Datatable
WHERE Date >= '2018-04-01'
ORDER BY Date ASC
当前的strCondition输出类似于：
"2.8,TEXT","TEXT,2.8","TEXT 2.8"
和其他变体形式。
我如何让它仅输出“ 2.8”？
您所需的信息不是100％清晰，但我知道您可以使用PatternSplitCM进行此类操作：
此：
DECLARE @string VARCHAR(8000) = '"2.8,TEXT","TEXT,2.8","TEXT 2.8"';
SELECT s.Item
FROM dbo.PatternSplitCM(@string,'[0-9.]') AS s
WHERE s.[Matched] = 1 AND TRY_CAST(s.item AS FLOAT) IS NOT NULL;
返回：
Item
-------
2.8
2.8
2.8
如果只需要一个，则可以添加一个TOP（1）以获取第一个。
此：
DECLARE @datatable TABLE (dID INT IDENTITY,strCondition VARCHAR(1000));
INSERT @datatable(strCondition)
VALUES ('Sometext... blah blah... 20191108 blah blah...'),('"More stuff 22.44","ggooggoo"');
SELECT d.strCondition,Item
FROM @datatable AS d
CROSS APPLY
(
SELECT TOP(1) s.Item
FROM dbo.PatternSplitCM(d.strCondition,'[0-9.]') AS s
WHERE s.[Matched] = 1 AND TRY_CAST(s.item AS FLOAT) IS NOT NULL
ORDER BY ItemNumber
) AS s;
将返回：
strCondition Item
------------------------------------------------------- ---------
Sometext... blah blah... 20191108 blah blah... 20191108
"More stuff 22.44","ggooggoo" 22.44
如果您不介意转换为MONEY（小数点后4位），那会比较宽容
示例
Declare @YourTable Table ([ID] varchar(50),[SomeCol] varchar(50)) Insert Into @YourTable Values
(1,'2.8,TEXT'),(2,'TEXT,2.8'),(3,'TEXT 2.8')
Select A.*,SomeVal = try_convert(money,value)
from @YourTable A
Cross Apply string_split(SomeCol,' ') B
Where try_convert(money,value) is not null
返回
ID SomeCol SomeVal
1 2.8,TEXT 2.80
2 TEXT,2.8 2.80
3 TEXT 2.8 2.80