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.