current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 1 down vote favorite

I have a dynamic SQL query which returns rows like below with string values & numeric values.

EMP col1 col2 col3 col4 col5
----------------------------
A1   4     4    3   3   3
A2   4     2    5   3   3
A3  sd     3    3   1   sd
A4   3     4    3   3   3

Now I need a new column which sums col1 to col5 and creates a total sum column where it should ignore the string values as in row 3. There are no NULL values

How could I achieve this? Using ISNUMERIC might be the solution, but I'm not sure how to use it in such a scenario.

share|improve this question
    
you could do a CASE WHERE col1 IN (1,2,3,etc...) then sum. (that's the basic idea anyway. – durbnpoisn Jul 31 '14 at 20:45
up vote 3 down vote accepted

You can use a CASE Expression to determine whether the value is a number. If it is a number then either cast the value to an INT or DECIMAL data type, otherwise use 0 so it doesn't effect the sum.

SELECT
     CASE WHEN ISNUMERIC(col1) = 1 THEN CAST(col1 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col2) = 1 THEN CAST(col2 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col3) = 1 THEN CAST(col3 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col4) = 1 THEN CAST(col4 as INT) ELSE 0 END
   + CASE WHEN ISNUMERIC(col5) = 1 THEN CAST(col5 as INT) ELSE 0 END as SumValue
FROM MyTable
share|improve this answer
    
OP does mention that they want to ignore the values in col3 – paqogomez Jul 31 '14 at 20:54
    
ah okk.. didn't think of using case when for each columns seperately.. – neophyte Jul 31 '14 at 20:55
    
@paqogomez No you miss read. The OP wrote "I need a new column which sums col1 to col5" and "it should ignore the string values as in row 3". It didn't ask for ignoring a column. Even so, this is just an example and the OP can modify to include or omit columns as needed. – Adam Porad Jul 31 '14 at 20:59
    
You are correct, I misunderstood. In any respect, I had already upvoted you. :) – paqogomez Jul 31 '14 at 21:08
up vote 2 down vote

If you're on SQL Server 2012, TRY_CONVERT avoids pitfalls commonly encountered with ISNUMERIC:

SELECT col1, col2, col3, col4, col5,
       ISNULL(TRY_CONVERT(int, col1), 0) +
       ISNULL(TRY_CONVERT(int, col2), 0) +
       ISNULL(TRY_CONVERT(int, col3), 0) +
       ISNULL(TRY_CONVERT(int, col4), 0) +
       ISNULL(TRY_CONVERT(int, col5), 0) AS total
FROM Employee

SQLFiddle

share|improve this answer
    
yeah i am using SQL SERVER 2012.. never used TRY_CONVERT before.. looks interesting.. I know about the pitfalls of ISNUMERIC coz I have faced them before myself.. but in this particular case for me it wont be a problem coz the numbers come from count function – neophyte Jul 31 '14 at 21:19
    
but it deserves an upvote too.. for introducing to a new SQL function – neophyte Jul 31 '14 at 21:25
up vote 1 down vote

You can do this with a big case statement:

select q.*,
       ((case when isnumeric(col1) = 1 then cast(col1 as int) else 0 end) +
        (case when isnumeric(col2) = 1 then cast(col2 as int) else 0 end) +
        (case when isnumeric(col3) = 1 then cast(col3 as int) else 0 end) +
        (case when isnumeric(col4) = 1 then cast(col4 as int) else 0 end) +
        (case when isnumeric(col5) = 1 then cast(col5 as int) else 0 end)
       ) as newcol  
from q;

isnumeric() should be sufficient for your purposes. You might need fancier logic if you only want positive integers or want to exclude exponential notations or the like.

share|improve this answer
up vote 0 down vote

You're on the right track with isnumeric:

select
emp,
(case when isnumeric(col1) = 1 then cast(col1 as int) else 0 end) +
col2...
from table1
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.