Link to home
Start Free TrialLog in
Avatar of tmajor99
tmajor99

asked on

SQL Try_Cast - how to gets failures only

I am using MS SQL and have i think a simple question.  I want to report all the values from a column that are not integer compliant values.  The Try_Cast will attempt to convert all values to INT and for any value that it cannot it displays null.  How can i just capture the nulls which are the failures?  

select  [ValueTest], TRY_CAST([ValueTest] AS int) as 'ConvertedValue' from [TestData] 

Open in new window

TestData
  ValueTest    ConvertedValue
  ---------    ----------------
   100         100 
   ABC       
  -3           -3 
   23.99     

How can i display only the nulls?  For example, expected results:
  ValueTest    ConvertedValue
  ---------    ---------------- 
   ABC       
   23.99  

Open in new window





ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

TryCast can also go in the WHERE clause?

select  [ValueTest]from [TestData] 
where  TRY_CAST([ValueTest] AS int) is null

Open in new window