Convert NVARCHAR to INT skipping non convertibles

Question

I need to query a database which is designed so what a NVARCHAR column keeps age of clients. I want group clients using

GROUP BY age / 10

but when engine encounters some string like '20.10.1984' (someone entered his bithday instead of age), my query fails.

The percent of "bad" ages is very small, so I want just to skip records which age isn't convertible to INT.

Is there an easy way to do it?

Thanks!

Answer 1

Add a WHERE IsNumeric(age) = 1

Like this:

CREATE TABLE #temp (age NVARCHAR(20))

INSERT INTO #temp
    SELECT '22'
    UNION
SELECT '24'
    UNION
SELECT '20.10.1984'
    UNION
SELECT '50'

SELECT age/10, COUNT(*)
    FROM #temp
    WHERE ISNUMERIC(age) = 1
    GROUP BY age/10

DROP TABLE #temp