Scalar-Valued Function: Convert to Number

[mada.hauptfleisch]

I have Table1 with a VARCHAR(MAX) column which contains values like 4, 1.4, 0.3, 4.25 and I'm trying to take these decimal values and populate them into another table (Table2) with a DECIMAL (18, 2) column with the following Scalar-Valued Function: Convert to Number. (The reason why the column in Table1 is VARCHAR(MAX) is because insertion takes place dynamically and we don't know beforehand what values they might be - so need to make provision for text as well as other values)

ALTER FUNCTION [dbo].[ConvertToNumber](@Value VARCHAR(MAX))
RETURNS DECIMAL (18, 2)
AS
BEGIN
    SET @Value = REPLACE(@Value, ',', '')
    IF ISNUMERIC(@Value + 'e0') = 0 RETURN NULL
    IF ( CHARINDEX('.', @Value) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 )RETURN NULL
    DECLARE @I DECIMAL (18, 2)
    SET @I = CASE
        WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(DECIMAL, PARSENAME(@Value, 2))
        ELSE CONVERT(DECIMAL, @Value)
        END
    -- IF ABS(@I) > 2147483647 RETURN NULL
    RETURN @I
END

The problem that is occurring with inserting the values with decimals i.e. 0.3 or 4.25 is that the values inserted are "NULL" -- the values without decimals do insert correctly.

Please - if anyone can have a look at the code and tell me what I'm doing wrong or how to change it so that the correct decimal values are inserted into Table2 DECIMAL column - I will really appreciate any help.

Many thanks

[BitBlt]

The problem is line 7. You're testing to see if there's a decimal point, then testing if there is a non-zero number after the decimal point...if there is, you return NULL. I'm not sure what you want to accomplish with that test...if you put in (for instance) 1234.0 then the test passes because the number returned by the "CONVERT...PARSENAME" piece is equal to zero. If you put 1234.01, then the number returned is 1...valid, but you cause it to return NULL.

Here's a little alteration of your function that I used to test the scenarios...maybe it will help you figure out what you want to actually do in the various possibilities, without hiding your interim results...makes for easier debugging.

declare @Value VARCHAR(MAX)
DECLARE @I DECIMAL (18, 2)
set @Value = '1234567.0'                  -- <----change this to other values for testing purposes --
SET @Value = REPLACE(@Value, ',', '')
IF ISNUMERIC(@Value + 'e0') = 0 
    begin
        select 'Isnumeric test failed'
    end
else
begin
select PARSENAME(@Value, 1), CONVERT(bigint, PARSENAME(@Value, 1))
IF ( CHARINDEX('.', @Value) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 )
    begin
        select 'Charindex test failed'
    end
else
    begin
        SET @I = CASE
        WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(DECIMAL, PARSENAME(@Value, 2))
        ELSE CONVERT(DECIMAL, @Value)
        END
    end
end

select @I

Hope this helps a little bit! Happy coding!

[mada.hauptfleisch]

Thank you very much for your reply.

I'm a bit of a "newbie" and are struggling with this.

I'm trying to take the decimal value in Table1 i.e 1.44 or 0.3 and insert it exactly the same into the Table2 DECIMAL value column and not let it insert a NULL value. (Don't know how to correct the code in order for this to happen)

Also if the value in Table1 VARCHAR column has more than 2 decimals i.e. 1.4467 will it insert a value of 1.45 into Table2 DECIMAL column or insert 1.44?

I thank you in advance for any assistance and will also test with your code example.

Regards

[BitBlt]

It will automatically round if you don't do anything else. IF that's okay, great. If not, you'll have to resort to other means such as the one you're using.