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

Answer 2

Because your dataType is string type and there is one column contain ''

You can try to use condition aggregate function in MIN and CAST to int

Select  ID, 
        min(CASE WHEN Amount like '%[0-9]%'  THEN CAST(Amount AS INT)  END) as TheMin
from @TempTable2
    unpivot (Amount for AmountCol in (Limit1,Limit2,Limit3,Limit4,Limit5)) as unpvt
group by ID

sqlfiddle

ID  TheMin
1   1000
2   2000

NOTE

I would suggest your columns turn to save int instead of varchar if you only store number in them.