About Pricing Community Teams Start Free Trial

tmajor99

asked on 8/5/2021

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

Last Comment

slightwv (䄆 Netminder)

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

8/5/2021

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)

8/5/2021

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

Plans and Pricing

Certified Expert Program

Credly Partnership

Udemy Partnership