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?
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.