CODEWITHSUNDEEP
sql-server
TheGeneral
TheGeneral

Reputation: 81573

How to determine if a field is numeric and convert in the same where clause

I have the following simple sql 

SELECT TOP 1000 *
  FROM CallRecords
  where clientId = 4 and 
  resultcodeId > 1 and
  ISNUMERIC(extension) = 1 and 
  Convert(int,extension) > 72320 and Convert(int,extension) < 73000

I'm getting the following error

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'b01004957003' to data type int.

Obviously one extension contain 'b01004957003'

However I thought sql would stop checking after the ISNUMERIC(extension) = 1

Any clues?

Upvotes: 0

Views: 75

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Try keeping the convert in outer query

SELECT *
FROM   (SELECT TOP 1000 *
        FROM   CallRecords
        WHERE  clientId = 4
               AND resultcodeId > 1
               AND Isnumeric(extension) = 1) A
WHERE  CONVERT(INT, extension) > 72320
       AND CONVERT(INT, extension) < 73000

Upvotes: 1

Related Questions