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





SQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
David Todd

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck