One way to resolve and calculate fractional strings
CREATE FUNCTION dbo.fnResolveFractionals
(
@data VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
RETURN CASE
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)
ELSE NULL
END
END
GO
DECLARE @Sample TABLE
(
data VARCHAR(20)
)
INSERT @Sample
SELECT '5 3/16' UNION ALL
SELECT '7' UNION ALL
SELECT '2 /' UNION ALL
SELECT '1 2/' UNION ALL
SELECT '5 /7' UNION ALL
SELECT '8 3' UNION ALL
SELECT '19 24/32' UNION ALL
SELECT '1024 784/32' UNION ALL
SELECT '8 3/0'
SELECT *,
dbo.fnResolveFractionals(data)
FROM @Sample
Legacy Comments
|
GeorgeV
2008-12-15 |
re: One way to resolve and calculate fractional strings I saw this question the other day too: http://www.dbforums.com/showthread.php?t=1636358 One thing I see in your function is that if you pass it a value of '1/2' it returns NULL because you're assuming there will always be a leading integer to play with. |
|
Peso
2008-12-15 |
re: One way to resolve and calculate fractional strings It is very easy to edit the CASE statement to deal with fractions only. I just put the complete integer plus fractional there because it is the hardest option. But thanks for pointing it out, there may be readers not knowing this. |