Menu
Is there a way to call the Sql Server functions Convert/Cast without having them throw an exception?
Basically, I have a column that contains alphanumeric data and I am extracting values from the field and I want to display the data as an integer value. Sometimes the data extracted is not a number and in those cases I want Sql Server to skip over that line (return null or 0).
Right now the Sql select statement fails with a "Conversion failed when converting the nvarchar value 'xxxxx' to data type int.
What I want is something similar to C#'s int.TryParse.
Suggestions?
814
To fix this, either convert the number to a string, or use a function like CONCAT() or CONCAT_WS() to perform the concatenation.
SQL Server's CAST() and CONVERT() methods can be used to convert VARCHAR to INT. We'll also look at the more efficient and secure approach to transform values from one data type to another.
CAST is also less powerful and less flexible than CONVERT. On the other hand, CONVERT allows more flexibility and is the preferred function to use for data, time values, traditional numbers, and money signifiers. CONVERT is also useful in formatting the data's format. What is this?
To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below: SELECT CAST('344' AS NUMERIC) AS NUMERIC; SELECT CAST('344' AS BIGNUMERIC) AS big_numeric; The queries above should return the specified value converted to numeric and big numeric.
select case when isnumeric(YourColumn + '.0e0') = 1 then cast(YourColumn as int) else NULL end /* case */ from YourTable If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
