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

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!

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.