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

Question

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'                    |
| '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?

Answer 1

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.