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