Is there a way to isolate float value from string directly in SQL Server?
I have tried the solution from Extract float from String/Text SQL Server but to no avail. Much appreciated!
SELECT strCondition, Amount1, Amount2, Amount3
FROM Datatable
WHERE Date >= '2018-04-01'
ORDER BY Date ASC
The current strCondition outputs are similar to:
"2.8, TEXT", "TEXT, 2.8", "TEXT 2.8"
and other variations.
How would I get it to output "2.8" only?
What you need is not 100% clear but I know you can use PatternSplitCM for this type of thing:
This:
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;
Returns:
Item
-------
2.8
2.8
2.8
You can add a TOP (1) to get the first one if you only need one.
This:
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;
Will Return:
strCondition Item
------------------------------------------------------- ---------
Sometext... blah blah... 20191108 blah blah... 20191108
"More stuff 22.44","ggooggoo" 22.44
If you don't mind converting to MONEY (4 decimals) which is a little more forgiving
Example
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
Returns
ID SomeCol SomeVal
1 2.8, TEXT 2.80
2 TEXT, 2.8 2.80
3 TEXT 2.8 2.80
