CODEWITHSUNDEEP
sql-servert-sqlstored-procedurestype-conversion
Nello
Nello

Reputation: 11

T-SQL converting string into float

In a stored procedure on my SQL Server, I am trying to convert values from a varchar column into a float format. The values into the varchar column are numbers with a sign at the beginning and a '.' before decimals.

Examples: '+0000000000000044.09' or '-0000000000114995.61'

If I try this: convert(float,mystring), it doesn't work. I have:

Error converting data type varchar to float

Is this kind of conversion possible? Or is there another way to convert a string with a sign and a '.' into a float?

Upvotes: 0

Views: 10752

Answers (2)

Garima
Garima

Reputation: 1

You can try using CAST(mystring as float) or TRY_CONVERT(float,mystring). Thuough convert(float,mystring) also should work fine. I would suggest checking your data.

Upvotes: 0

Matt Gibson
Matt Gibson

Reputation: 38238

As your examples both work, I'd guess there's another value somewhere in your table that's causing the problem. In recent versions of SQL Server (2012 onwards), the TRY_CONVERT function can be useful for tracking down this kind of issue.

TRY_CONVERT will not throw an exception on a conversion failure, but instead return a NULL value, so you can figure out which values are causing the problem like this:

SELECT * FROM your_table WHERE TRY_CONVERT(FLOAT, your_column_name) IS NULL

If any rows are returned, those are the rows with problem values that can't be converted to FLOAT.

Upvotes: 1

Related Questions