Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

Simple base converter

Here is a simple base converter that manages [from and to] base 2 to 16.

CREATE FUNCTION    dbo.fnBaseConvert
(
    @Value VARCHAR(8),
    @FromBase TINYINT,
    @ToBase TINYINT
)
RETURNS VARCHAR(32)
AS
BEGIN
    RETURN (
                SELECT     SUBSTRING('0123456789abcdef', 1 +(x.theValue % CAST(POWER(CAST(@ToBase AS FLOAT), v.Number + 1) AS BIGINT)) / CAST(POWER(CAST(@ToBase AS FLOAT), v.Number) AS BIGINT), 1)
                FROM        (
                                SELECT SUM((CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef') - 1) * CAST(POWER(CAST(@FromBase AS FLOAT), Number) AS BIGINT)) AS theValue
                                FROM    master..spt_values
                                WHERE   Type = 'P'
                                        AND Number < LEN(@Value)
                                HAVING MIN(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) > 0
                                        AND MAX(CHARINDEX(SUBSTRING(@Value, LEN(@Value) - Number, 1), '0123456789abcdef')) <= @FromBase
                            ) AS x
                INNER JOIN  master..spt_values AS v ON v.Type = 'P'
                WHERE       x.theValue >= 0
                            AND v.Number < CEILING(0.00000005 + LOG(COALESCE(NULLIF(x.theValue, 0), 1)) / LOG(@ToBase))
                            AND @FromBase BETWEEN AND 16
                            AND @ToBase BETWEEN 2 AND 16
                ORDER BY    v.Number DESC
                FOR XML     PATH('')
            )
END
 

Print | posted on Tuesday, July 21, 2009 1:18 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# Phentermine.

Phentermine.
8/23/2011 5:30 PM | Phentermine.
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET