Link to home
Start Free TrialLog in
Avatar of 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

  ValueTest    ConvertedValue
  ---------    ----------------
   100         100 
  -3           -3 

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

Open in new window

Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
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