Numbers

These functions are for working with numbers.

fn_hex_to_int

This function converts a hexadecimal string into an integer.

CREATE FUNCTION [dbo].[fn_hex_to_int](
                                        @hex VARCHAR( 8 ) )
RETURNS INT
-- Ver. 1.0.0 28.Oct.2001, Ofer Bester
AS
BEGIN
    DECLARE @i TINYINT,
        @nibble TINYINT,
        @ch CHAR( 1 ),
        @result INT;
    SET @i = 1;                                                     -- Init nibble counter
    SET @result = 0;                                                -- Init output parameter
    SET @hex = UPPER( LTRIM( RTRIM( @hex ) ) );                     -- Convert to uppercase
    WHILE( @i <= LEN( @hex ) )
    BEGIN
        SET @ch = SUBSTRING( @hex, @i, 1 );
        IF( @ch >= '0'
        AND
        @ch <= '9' )
        SET @nibble = ASCII( @ch ) - ASCII( '0' );
        ELSE
        IF( @ch >= 'A'
        AND
        @ch <= 'F' )
        SET @nibble = ASCII( @ch ) - ASCII( 'A' )+10;
        ELSE
        RETURN NULL;
        IF( @result > 0x7FFFFFF )                                   -- 134217727 = 0x7FFFFFF
        BEGIN
            SET @result = @result&0x7FFFFFF;                        -- Set MSB, of 7 nibbles, OFF
            SET @result = @result * 16 + @nibble + 0x80000000;      -- Shift left 4Bits, Add last nibble and convert to negetive number.
        END;
        ELSE
        BEGIN
            SET @result = @result * 16 + @nibble;                   -- Shift left 4Bits, Add nibble.
        END;
        SET @i = @i + 1;                                            -- Next nibble.
    END;                                                            -- While
    RETURN( @result );
END;                                                                -- Function
GO

fn_is_integer

This function checks to see if the input is an integer.

CREATE FUNCTION [dbo].[fn_is_integer](
                                         @Value VARCHAR( 18 ) )
RETURNS BIT AS
BEGIN
    RETURN ISNULL(
    ( SELECT
            CASE
                WHEN CHARINDEX( '.', @Value ) > 0
                THEN
                    CASE
                        WHEN CONVERT( BIGINT, PARSENAME( @Value, 1 ) ) <> 0
                        THEN 0
                        ELSE 1
                    END
                ELSE 1
            END
        WHERE ISNUMERIC( @Value + 'e0' ) = 1 ), 0 );
END;
GO

fn_remove_non_numeric

This function removes all non-numeric characters from the input.

CREATE FUNCTION [dbo].[fn_remove_non_numeric](
                                                  @strtext NVARCHAR( MAX ) )
RETURNS NVARCHAR( MAX ) AS
BEGIN
    DECLARE @result NVARCHAR( 255 );
    WHILE PATINDEX( '%[^-.0-9]%', @strtext ) > 0
    BEGIN
        SET @strtext = STUFF( @strtext, PATINDEX( '%[^-.0-9]%', @strtext ), 1, '' );
    END;
    SELECT @result = @strtext;

    RETURN @result;
END;
GO