15,666,479 members
See more:
Hi,
I have a DataTable StudentsDtb having String DataType Column roll_no
roll_no holding data's like the following
C#
```1
2
3
3a
3b
4
5
6
7
8
9
10
11
11a
12
13
13a
13b

From the above data I wish to have a max(roll_no) as 13. Is I possible?

object MyComputeValue=StudetsDtb.Compute(Max(roll_no),"");??????```

Thanks

What I have tried:

Tried to find a Maximum Value from a String Column DataTable
Posted
Updated 18-Jun-20 2:51am

## Solution 1

try this:

SQL
`select max(case when isnumeric(roll_no)=0 then 0 else roll_no end) as max_roll`

if there's a chance your data will not always have a simple numeric value (in other words:

1
2
3b

you probably want to try this

SQL
```SELECT MAX(SUBSTRING(string, PATINDEX('%[0-9]%', roll_no), PATINDEX('%[0-9][^0-9]%', roll_no + 't') - PATINDEX('%[0-9]%',
roll_no) + 1)) AS max_roll```

v3
Paramu1973 17-Jun-20 11:25am
Hi, how to use this select statement like any codes? thanks
#realJSOP 17-Jun-20 12:06pm
I have no idea what you're asking
Maciej Los 18-Jun-20 8:54am
5ed!

## Solution 2

This is a bit clunk but should work:
SQL
```SELECT MAX(
CASE WHEN T.roll_no NOT LIKE '%[^0-9]%' THEN CAST(T.roll_no AS INT) ELSE NULL END
)
FROM Table AS T  ```

This will look at each entry: if there are non-numeric characters in it, it will return null, otherwise, it will convert the string into an integer. MAX() will filter out the nulls, and return the maximum integer.

The only caveat is that if you have '14a' and '14b' without '14', then 13 will be returned as the max.

Maciej Los 18-Jun-20 8:54am
5ed!

## Solution 3

If you would like to get the highest number from c# code, check this out:
C#
```DataTable dt = new DataTable();

int highestNumber = dt.AsEnumerable()
.Max(x=> Convert.ToInt32(Regex.Split(x.Field<string>("roll_no"), @"[a-zA-z]")[0]));
//result: 13```