Menu
PRINT CONVERT(NUMERIC(18,0), '')
produces Error converting data type varchar to numeric.
However,
PRINT CONVERT(INT, '')
produces 0 without error...
Question: Is there some SQL Server flag for this or will I need to do case statements for every varchar to numeric conversion? (aside from the obvious why?)
456
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
In our demo, that is performed using three different data types conversions, it can be found that CAST is the fastest function that can be used to convert the data type of the provided value, and the PARSE function is the slowest.
CONVERT is SQL Server specific, CAST is ANSI. CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST .
Related SQL FunctionsCAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER. TO_DATE converts a formatted date string to a date integer.
Use ISNUMERIC
declare @a varchar(20)
set @a = 'notanumber'
select case when isnumeric(@a) = 0 then 0 else convert(numeric(18,0),@a) end
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
