CODEWITHSUNDEEP
sqlsql-serverstringt-sqlcase
Tom Camish
Tom Camish

Reputation: 69

SQL CASE statement needs to handle Text

Apologies if this has been asked before - I've spent a couple of hours searching but not found anything that's helped.

It's quite simple really - I've been asked to create a query which includes a field that when it was set up (not by me) was created as a VARCHAR instead of an INT.

I need to do some calculations on this field, however some users have been entering text into it, so the calculations fail as it can't convert the data to an INT.

Is there anything I can add to a CASE statement to handle where there's text?

I was thinking something like the below, but don't know what the actual code is:

CASE
    WHEN [Field1] IS TEXT THEN 1 ;
    ELSE [Field2] as [Chose name]
END

Edit: Note that this is in MS SQL Server. Thanks.

Upvotes: 0

Views: 1097

Answers (2)

AVTyagi
AVTyagi

Reputation: 1

In SQL Server

Declare @Salary varchar(100);                                
Set @Salary = 50000;                                       
Select Case when ISNUMERIC(@Salary) = 1 then 1 
            else 0 end as [Check]

May be this will be Helpful.

Upvotes: 0

GMB
GMB

Reputation: 222412

In SQL Server, you can use try_convert() and isnull() for this:

isnull(try_convert(int, field), 1)

try_convert() attempts you cast field to an int. When that fails, null is returned; you can trap that with isnull() and turn the result to 1 instead.

Note that this only works as long as field is not null (otherwise, you would get 1 as a result).

Upvotes: 3

Related Questions