Convert NVARCHAR to INT skipping non-convertible

I need to query a database that is designed so that the NVARCHAR column keeps the ages of the clients. I want group clients to use

GROUP BY age / 10 

      

but when the engine encounters some string like '20 .10.1984 '(someone has entered their bithday instead of age) my query doesn't work.

The percentage of "bad" ages is very small, so I just want to skip records whose ages are not converted to INT.

Is there an easy way to do this?

Thank!

0


source to share


1 answer


Add 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   

      

+4


source







All Articles