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.

Answer

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).