CODEWITHSUNDEEP
sqltype-conversion
Amir
Amir

Reputation: 1087

SQL convert nvarchar to float

I have a table with a column Quantity; in the original table this column is defined as nvarchar(100) so I need to cast it as float to be able to do some analysis:

CAST([Quantity] AS FLOAT) AS Quantity

The issue is that I have some values which can not be converted to float like No-Quantity, Return etc. I to have filter to exclude these values and then convert rest to float.On option is use where clause:

WHERE Quantity IN ('Return', 'Sales')

This is not the best way since if we have anew values in the original table then I need to figure out what it is and add it to the where clause.

I am wondering is there is better way to identify non-convertible values?

Upvotes: 11

Views: 93234

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

In any database, you can use cast() and something like this:

(case when quantity not in ('No-Quantity', 'Return', . . .)
      then CAST([Quantity] as float)
 end) as Quantity

The in list would be the list of known string values.

You can also do a fast-and-dirty check like this:

(case when left(quantity, 1) between '0' and '1'
      then CAST([Quantity] as float)
 end) as Quantity

(Note: you may need to use substr() or substring() instead of left().)

And, in general, any specific database has specific functions that can help with the conversion, such as try_convert() mentioned in a comment.

And if you want to have original non convertable values instead of nulls:

(case when DESIRED_CRITERIONS
      then CONVERTED_OUTPUT
      else quantity
 end) as   Quantity

Upvotes: 4

CSmith
CSmith

Reputation: 13458

If your SQL Server supports TRY_CONVERT, this could provide a nice solution:

SELECT TRY_CONVERT (FLOAT, [Quantity]) ...

Will give you the converted values or NULL, depending on the input. This could be helpful if you do not have strict control over the data.

Upvotes: 8

Horaciux
Horaciux

Reputation: 6477

Another way (if you can't use TRY_CONVERT)

SELECT CAST(quantity AS float)
FROM myTable
WHERE IsNumeric(quantity) = 1 AND quantity IS NOT NULL

Upvotes: 1

Related Questions