Conversion failed when converting the nvarchar value to a data type int

Question

I have a query that I'm attempting to check the scode column for a range of code numbers. The problem is the scode column includes some nvarchar characters, and errors when it finds them.

How can I run the following query, without returning errors.

SELECT dtExpires, dtFirst
FROM Customers
WHERE (scode BETWEEN 10 AND 100) OR
      (scode BETWEEN 500 AND 600)

Error I'm receiving:

Conversion failed when converting the nvarchar value to a data type int

Answer 1

If you can't FIX THE DATA TYPE which is really what you SHOULD be doing...

The only reliable way to short-circuit is to stuff filtered results into a #temp table. Otherwise you have no control over order of evaluation. Even if you use a subquery or CTE to filter rows where ISNUMERIC() = 1 "first", SQL Server might evaluate the BETWEEN conditional first, and still fail with the same error. This may not always happen, but this is the only way to guarantee you avoid it:

SELECT dtExpires, dtFirst, scode
INTO #t
FROM dbo.Customers
WHERE scode NOT LIKE '%[^0-9]%'; -- this assumes no decimals allowed

SELECT dtExpires, dtFirst
FROM #t
WHERE (scode BETWEEN 10  AND 100) 
   OR (scode BETWEEN 500 AND 600);

DROP TABLE #t;

Answer 2

Your ultimate problem here is that you are storing these number values as nvarchar. This is problematic for several reasons first of is speed. There are tons of great documents and answers out there showing this is the case many of them on this very Website.

Integer values are smaller than character strings you can fit much more per page than you can with nvarchar and mathematics are more easily done on integers. Try a little experiment go to SQLFiddle and enter your table and give it some values and run your query. Then try changing that column to an integer value and running the same query not only will it run faster but your current SELECT statement will run properly. If you ever need to join on this information and integer will me much faster as well.

I'm not entirely sure as to weather or not you have the power to do this or not but it will certainly fix your problem and improve your database performance as well.

Answer 3

Maybe use something like

SELECT        dtExpires, dtFirst
FROM            Customers
WHERE        ( cast(fnRemoveNonNumericCharacters(scode) as int) BETWEEN 10 AND 100) OR
                     (cast(fnRemoveNonNumericCharacters(scode) as int)BETWEEN 500 AND 600)

where the function is :

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

or if you want to exclude those entries where scode is not numeric make also a bool returning function that will tell you if it has non numeric chars and put it in the where clause.

also a way to solve this might be: (inspired a lil bit by Aron

SELECT        dtExpires, dtFirst
FROM            Customers
WHERE        (( case when scode LIKE '%[^0-9]%' then 0 else scode end BETWEEN 10 AND 100) OR
                     (case when scode LIKE '%[^0-9]%' then 0 else scode end BETWEEN 500 AND 600))
and scode not like '%[^0-9]%'