SQLServer 2005 - change character in NVarChar
I am getting some arguments to a stored procedure. These arguments are NVARCHAR.
I have a problem where I need to pass some of these FLOATS values because they are accepted, for example
@ VALUE1 NVARCHAR (100)
DECLARE @ChangedValue SET @ChangedValue = CAST (@ Value1 AS FLOAT)
eg. @ Value1 = "0.001"
Gives me a problem as it expects "0.001"
I cannot change the input format, but can I somehow change it on the SQL server side? By changing all "," to "." instead of this?
Best wishes Kenn
source to share
As Mitch said, this doesn't sound so good, and it would be much better if the provided parameter was float in the first place. He gave you a solution that solves your problem at the moment, but as he pointed out, you may face some problems in the future.
I am assuming that the procedure is being called by some application code, and I suspect that these numbers are entered by the user. If this is the case, then the conversion must happen at the application level, as this will concern the locale settings, and this is the only place that has control over the user input format.
source to share
I'm not familiar with your data, but make sure you don't have any values that are valid with a comma, such as 1000. Changing this parameter to 1.000 with a replacement changes the actual number, which will be drastically involved.
I would fix the UI so that invalid numbers cannot be put into 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 valid number after clearing the data. This way, bad data can't get into the field to start with.
source to share