How to extract max number from string using LINQ

Question

I have some things in my database like-
1425jewel8521
1426Samim9875
8925jewe1459

expected as result like this
Result1=8925
Restult2=9875

From here I want to get the maximum value from the four integer values ​​on the left side. Similarly, I want to get the maximum value from the last four numeric values ​​on the right side. I need to find it using LinQ Quarry. I would appreciate it if someone could help me in this matter. thanks in advance

Answer 1

you need to read the table into memory, than parse on c# side:

	var list = db.MyTable
        .Select (r => r.MyColumn)
		.ToList()
		.Select(r =>
		{
			var match = Regex.Matches(r,"\\d+");
			return Tuple.Create(Int32.Parse(match[0].Value), Int32.Parse(match[1].Value));
		});
		
	Console.WriteLine(list.Max(r => r.Item1));
	Console.WriteLine(list.Max(r => r.Item2));

Answer 2

The main problem is the design breaks database design rules called normalization. No matter what approach you choose, all the data from the table must be read. Next logic converts the data encoded string into properly normalized data by to splitting up the compound string into three columns. Once the data is normalized, then an aggregate function like Max() can be used.

A much better design is creating a table with three columns to hold the first number, the text, and the second number.

With that being said, there are many ways to solve a string manipulation problem. The example below finds the index where the character changes from a number to a string. Keep in mind, this approach only works for 3 columns and assumes you already fetched the strings into a list.

List<string> strings = new List<string>() { "1425jewel8521", "1426Samim9875", "8925jewe1459" };

List<MyData> data = new List<MyData>();

foreach (string s in strings)
{
    int col = 1;
    int col1 = 0;
    int col2 = 0;
    int col3 = 0;

    for (int i = 0; i < s.Length; i++)
    {
        //Number
        if (s[i] >= 0x30 & s[i] <= 0x39)
        {
            if (col == 1)
            {
                col1= i;
            }
            else
            {
               col3 = i;
            }
        }
        else //Text
        {
            col = 2;
            col2 = i;
        }
    }

    Console.Write(s.Substring(0, col1 + 1));
    Console.Write('\t');
    Console.Write(s.Substring(col1 + 1, col2 - col1));
    Console.Write('\t');
    Console.Write(s.Substring(col2 + 1, col3 - col2));
    Console.WriteLine();

    MyData record = new MyData();
    record.Number1 = int.Parse(s.Substring(0, col1 + 1));
    record.Text = s.Substring(col1 + 1, col2 - col1);
    record.Number2 = int.Parse(s.Substring(col2 + 1, col3 - col2));

    data.Add(record);
}

Console.WriteLine("-------------------------------------");
Console.WriteLine($"Result1 = {data.Max(d => d.Number1)}");
Console.WriteLine($"Result2 = {data.Max(d => d.Number2)}");


public class MyData
{
    public int Number1 { get; set; }
    public string Text { get; set; }
    public int Number2 { get; set; }
}