How to ignore string value using unpivot function finding the min value among other columns

Question

I have a table looks like this

The goal is to find a min value among columns Limit (Limit1, Limit2,Limit3...etc).

For that, I am using UNPIVOT function.

But the problem is that some values are empty strings. As a result the min value becomes empty string.

declare @TempTable2 table (ID int, limit1 varchar(50),limit2 varchar(50),limit3 varchar(50),limit4 varchar(50),limit5 varchar(50) )
insert into @TempTable2 values   (1,'1000','1000','3000',NULL, NULL)
                                ,(2,'2000','4000','3000','', NULL)

--select * from @TempTable2
Select  ID, 
        min(Amount) as TheMin
from @TempTable2
    unpivot (Amount for AmountCol in (Limit1,Limit2,Limit3,Limit4,Limit5)) as unpvt
group by ID

So how can I ignore string values when using UNPIVOT function?

I would expect my result be like this:

ID  TheMin
1   1000
2   2000

I tried to use NULLIF but UNPIVOT doesnt accept it. Thank you

Answer 1

Just another options is NullIf()

Example

Select  ID, 
        min(nullif(Amount,'')) as TheMin
from @TempTable2
    unpivot (Amount for AmountCol in (Limit1,Limit2,Limit3,Limit4,Limit5)) as unpvt
group by ID

Returns

ID  TheMin
1   1000
2   2000