# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## 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

Print | posted on Monday, December 15, 2008 3:01 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

## #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.
12/15/2008 3:51 PM | GeorgeV

## #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.
12/15/2008 10:31 PM | Peso
Comments have been closed on this topic.

Powered by: