Search code examples
sqlsql-servercastingconverters

How to convert nvarchar to int

I need to get data according to nvarchar column that holds numbers, I get this error:

Conversion failed when converting the nvarchar value '362X' to data type int.

I tried this:

select modelcode 
from model 
where cast(modelcode as int )> 10000 - ERROR
where cast(modelcode as bigint )> 10000 - ERROR
where pws_modelcode+0 > 10000 - ERROR
order by ModelCode asc

What am I missing ?

Solution

  • In SQL Server, you can use try_convert() or try_cast():

    where try_cast(modelcode as int) > 10000

    In other databases, you can use a case perhaps with regular expressions:

    where (case when modelcode ~ '^[0-9]+$' then cast(modelcode as int) end) > 10000