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?

marc_s · Accepted Answer · 2020-10-03 07:19:25Z

4

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!

answered Oct 3, 2020 at 7:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server function does not work as I expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related