Finding Max Value of an nvarchar column in sql server 2012 using LINQ

Question

I have a table full of serial numbers of a product, the datatype is nvachar, i have entries like 1001, 1002, 1003, abc001, efg002, 1004, def222 . I have to return the max value of the valid numeric numbers in the column, in this case output should be 1004.

This SQL Query is working

select top 1
  cast(SerialNo as integer)
from [InvSerialItemDetails]
where isNumeric(SerialNo) = 1
Order by cast(SerialNo as integer) desc

I have to do the same in LINQ. But there is no isNumeric function in LINQ. How to get the same output in LINQ in VB language

I have the same question. But I want to return result of ordering. My concern is to return all the result by ordering in any case either ascending or descending. — Keyur Mistry, May 25 '16 at 09:49

score 0 · Answer 1 · answered Mar 06 '14 at 05:42

0

If you're using Linq to Entities, there is a rather hidden IsNumeric function in Linq.

answered Mar 06 '14 at 05:42

Joachim Isaksson

176,943
25
281
294

Russell Jonakin · Answer 2 · 2014-03-06T17:45:38.250

Her is an example in VB.Net. I just used a List of strings and the LINQ expression will grab the highest number in the List.

    Dim nvarcharNubers As List(Of String) = New List(Of String)

    nvarcharNubers.Add("abc1007")
    nvarcharNubers.Add("1002")
    nvarcharNubers.Add("1003")
    nvarcharNubers.Add("1004")
    nvarcharNubers.Add("2000fg")
    nvarcharNubers.Add("999")

    Dim TopNumberOnly = From i In nvarcharNubers
                      Where IsNumeric(i)
                      Select i = Integer.Parse(i)
                      Order By i Descending
                      Take 1

or if you wanted to use Lambda statements then:

    Dim TopNumberOnly = nvarcharNubers _
                        .Where(Function(w) IsNumeric(w)) _
                        .Select(Function(s) Integer.Parse(s)) _
                        .OrderByDescending(Function(o) o) _
                        .Take(1)

SpiderCode · Answer 3 · 2014-03-06T10:48:32.073

-1

If you are sure that your database field will only contain numeric value than you can always use max for nvarchar field. See below example :

var serials = new List<SerialObject>();

// Add AlphaNumeric value to the list
serials.Add(new SerialObject()
                {
                    Id = "1",
                    Serial = "aa10a1"
                });

// Add AlphaNumeric value to the list
serials.Add(new SerialObject()
{
    Id = "2",
    Serial = "cc11da"
});

// Add Numeric values to the list
for (int i = 0; i < 5; i++)
{
    serials.Add(new SerialObject()
                    {
                        Id = i.ToString(),
                        Serial = (1000 + i).ToString()
                    });
}

// Fetch max numeric from list using regular expression
var regex = new Regex("^[0-9]*$");

var numbers = serials.Where(a => regex.IsMatch(a.Serial)).Select(a => a.Serial).ToList();

var max = numbers.Max(a => a);

Serial Object Class

public class SerialObject
    {
        public string Id { get; set; }

        public string Serial { get; set; }
    }
}

edited Mar 06 '14 at 10:48

answered Mar 06 '14 at 06:01

SpiderCode

10,062
2
22
42

There would be alphanumeric values in the column, so cannot use max – user2922279 Mar 06 '14 at 09:42
For that you should use regular expression. have a look at my updated answer. Hope this will help you to solve your problem. Don't forget to upvote and mark it as an answer. – SpiderCode Mar 06 '14 at 09:58
Would it be applicable for a column with thousands of data mixed with numbers and varchars? – user2922279 Mar 06 '14 at 12:07
Yes. It will get only numeric records and get max number out of them. – SpiderCode Mar 06 '14 at 12:07
Note that in my sample code, I have provided code in c#. so you will have to change it to VB. – SpiderCode Mar 06 '14 at 12:29

Finding Max Value of an nvarchar column in sql server 2012 using LINQ

3 Answers3