question

artistlover avatar image
artistlover asked

varchar field

I have varchar field that I need to sum. select CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), a.quantity_ordered )) = 1 then CONVERT(VARCHAR(12), a.quantity_ordered) else 0 End) from [dbo].[xx] a Below is error Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '126.000' to data type int. I can drop the .000 on all fields if I new how. can anyone help?
varchar
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mjharper avatar image
mjharper answered
Hi, I don't think you need the outer convert. In the CASE the convert needs to be to a numeric datatype. Example below. Cheers Matt CREATE TABLE #Test ( Id INT IDENTITY(1, 1), quantity_ordered VARCHAR(50) ); INSERT INTO #Test VALUES ('abc'), ('126.00'); SELECT CASE WHEN ISNUMERIC(CONVERT(VARCHAR(12), a.quantity_ordered)) = 1 THEN CONVERT(DECIMAL(18, 9), a.quantity_ordered) ELSE 0 END FROM #Test a;
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

so that solved one problem and created another. select item_key, CONVERT(INT, CASE WHEN ISNUMERIC(CONVERT(VARCHAR(12), a.quantity_ordered)) = 1 THEN CONVERT(DECIMAL(18, 9), a.quantity_ordered) else 0 End) from [dbo].[xx] a group by Item_Key, QUANTITY_ORDERED it brings back duplicates instead of summing the items. Thoughts?
0 Likes 0 ·
You don't need Quantity_Ordered in the Group By. And you need a SUM around the outer convert: select item_key, SUM(CONVERT(INT, CASE WHEN ISNUMERIC(CONVERT(VARCHAR(12), a.quantity_ordered)) = 1 THEN CONVERT(DECIMAL(18, 9), a.quantity_ordered) else 0 End)) from [dbo].[xx] a group by Item_Key
0 Likes 0 ·
Oleg avatar image
Oleg answered
From the question definition the version of SQL Server is not clear, but if it is 2012 or higher then it would be a very good idea to avoid using ISNUMERIC. This function merely determines whether there is some way to convert the value to one of the available numeric data types. This does not mean, however, that the conversion will succeed even though the ISNUMERIC returned 1. For example run select isnumeric('$'), isnumeric('2e3'), isnumeric('.') ISNUMERIC returns 1 for all of them, but try to cast/convert any of these to decimal or integer. If the SQL Server in question is 2012 or higher, it is easier to use the [try_cast][1] function. This will allow restating the select statement in question like this: select isnull(try_cast(a.quantity_ordered as decimal(12, 3)), 0) quantity_ordered from dbo.xx a; The resulting expression will return the numeric value (converted to decimal) for any values that can be converted, otherwise NULL. Using isnull around it allows the resulting values to be either converted numbers or the zeroes for those values which cannot be converted. As @mjharper already pointed out in the comments, the **quantity\_ordered** column is ***not*** to be included in the group by clause. The ISNULL is not needed either because the SUM disregards the null values anyway, so with try\_cast, the query may look like this: select a.item_key, sum(try_cast(a.quantity_ordered as decimal(12, 3))) quantity_ordered from dbo.xx a group by a.item_key; Hope this helps. Oleg [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.