SQLServer 2005 - Change character in NVarChar

Question

I receive some arguments into a stored procedure. These arguments are NVARCHAR's.

I have a problem, when I need to cast some of these values to FLOATS, because they are being received as e.g.

@VALUE1 NVARCHAR(100)

DECLARE @ChangedValue SET @ChangedValue = CAST(@Value1 AS FLOAT)

E.g. @Value1 = "0,001"

Gives me a problem, as it expects "0.001"

I can't change the format of the input, but can I somehow change it on the SQL-server side? By changing all "," to "." instead?

Best regards, Kenn

Answer 1

You could use @VALUE1 = REPLACE(@VALUE1, ',', '.')

This does seem a horrible thing to do though!

Answer 2

Thank you.

But if it's horrible is there a better solution instead? :-)

Answer 3

As Mitch said it does not sound that good and it would be much better it the provided parameter was of type float in first place. He gave you the solution that solves your problem for now but as he pointed out you may get into some trouble in the future.

I assume that the procedure is called by some application code, and I would suspect that those numbers are user input. If that is the case then the conversion should happen on the application level as that would take care of locale settings and that is the only place that has control over the format of the user input.

Answer 4

Your right on your assumptions, and I am the first to admit, that this is not a solid solution. It does the job for now, but as you point out, more "counter measures" has to be taken.

Best regards, and thanks Kenn

Answer 5

I'm not familiar with your data, but make sure that you don't have any values which are valid with a comma such as 1,000. Changing that to 1.000 using replace changes the actual number involved drastically.

I would fix the user interface so that invalid numbers can't be placed in the field. I would also put a constraint on the table in the database that requires the number to be within a certain range or a valid number once you have the data cleaned up. That way bad data cannot get into the field to begin with.