SQL Server function does not work as I expected

Question

I got a SQL Server function. When I use as query command, it works fine, but when I put in a function, it does not work every time.

Here is my function

CREATE FUNCTION dbo.ConvertToFloat
    (@number_str nvarchar)
RETURNS float
AS 
BEGIN
    RETURN CAST(REPLACE(REPLACE(REPLACE(@number_str, '$', ''), '%',''),',','') AS float);
END

Example:

select dbo.AmazonNumberStrToFloat('1234.12') // return 1

but when I select

cast(replace(replace(replace('$1,123.45', '$', ''), '%',''),',','') as float) // return 1123.45

Am I doing anything wrong?

Answer 1

You should NEVER EVER define a SQL Server variable or parameter as varchar or nvarchar WITHOUT providing an explicit length!

If you omit the length - it will default to exactly 1 character - which usually isn't what you want or expect.

So replace this.

CREATE FUNCTION dbo.ConvertToFloat
    (@number_str nvarchar)

with something more sensible like

CREATE FUNCTION dbo.ConvertToFloat
    (@number_str NVARCHAR(25) )

or whatever length makes sense here, for you - but you MUST PROVIDE a length!