Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

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

# 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:
Powered By Subtext Powered By ASP.NET