SQL Server 2005 Convert VARCHAR to INT but default on invalid type

I have a varchar(100) column in a table that contains a mix of integers (as strings) and non-integer strings. E.g.

| dimension varchar(100) |
| '5'                    |
| '17'                   |
| '3开发者_JS百科'                    |
| 'Pyramids'             |
| 'Western Bypass'       |
| '15'                   |

How can I write an expression to, e.g. sum up all the values that are valid integers? If I were to try:

-- should return 5 + 17 + 3 + 15 = 40
SELECT
    SUM( CONVERT( INT, dimension ) )
FROM
    mytable

I would receive a Conversion failed when converting the varchar value 'Pyramids' to data type int. error.

Is there a test I can use in my expression, much like the ISNULL() function, that permits me to specify a default value if the field is not a number?

Try this:

SELECT
    SUM(CASE ISNUMERIC(dimension) 
           WHEN 1 THEN CONVERT( INT, dimension ) 
           ELSE 0  
        END)
FROM
    mytable

The CASE should check whether dimension is numeric - if so, return that value. If it's not numeric, return a default value (here: 0)

Is you need to query that quite frequently, you could also add a persisted, computed column to your table which encapsulates this computation and stores the value. This way, when summing and so on, you're not always re-computing the value:

ALTER TABLE mytable
  ADD NumericValue AS CASE ISNUMERIC(dimension) 
      WHEN 1 THEN CONVERT( INT, dimension ) ELSE 0 END PERSISTED

Now, you can SELECT dimension, numericvalue FROM mytable and get both values without any computation having to be executed.

You're going to run into overflow problems if you have a varchar like 88888888888888888888888

select
SUM(
    CASE 
      WHEN ISNUMERIC(CheckCol+ '.0e0') = 1  AND 
      convert(decimal(38,0), CheckCol) 
      BETWEEN -2147483648 AND 2147483647 
    THEN  1
    ELSE 0
    END
)  
from Table