Datatable split column value based on character in c#

Question

I want to split the column of a datatable like below

Message
--------
PR-111: test message
PR-112 - test message new
PR-113 : test message new2

It should split into a datatable like below

PRNumber    Message
------  -----------
PR-111  test message
PR-112  test message new
PR-113  test message new2

Note: There may be space before after the : or -

I have tried below but not giving proper result

 var r = dt1.AsEnumerable().Select(g => new {
           
            PRNumber = g["Message"].ToString().Contains(":") == true ? g["Message"].ToString().Split(':')[0] : g["Message"].ToString().Split('-')[0],
            Message = g["Message"].ToString().Contains(":") == true ? g["Message"].ToString().Split(':')[0] : g["Message"].ToString().Split('-')[1]
        }).ToList();

Can someone help on this please?

Answer 1

Since you want to create a new DataTable with two columns, you should create it first:

DataTable dt2 = new DataTable();
dt2.Columns.Add("PRNumber");
dt2.Columns.Add("Message");

Now a simple foreach is the best way to fill it, LINQ doesn't help you:

foreach(DataRow row in dt1.Rows)
{
    string message = row.Field<string>("Message");
    // now use String.Split with two delimiter stings including the spaces
    string[] cols = message.Split(new[]{" : ", " - "}, StringSplitOptions.None);
    dt2.Rows.Add(cols.First().Trim(), cols.Last().Trim());
}

---

Since you have added now some edge cases like these:

DataTable dt1 = new DataTable();
dt1.Columns.Add("Message");
dt1.Rows.Add("PR-111: test message");
dt1.Rows.Add("PR-112 : test message");
dt1.Rows.Add("PR-113 - test message new");
dt1.Rows.Add("PR-114 : test message new2");
dt1.Rows.Add("PR - 115 : test message new3");

It doesn't make any sense to use string.Split anymore, you need a simple parser, for example:

(string Number, string Message) ParseMessage(string message)
{
    if(!message.StartsWith("PR", StringComparison.OrdinalIgnoreCase))
    {   
        return (null, message);
    }

    var prPart = message.TakeWhile(c => !char.IsDigit(c));;
    var prNumberPart = message.Skip(prPart.Count()).TakeWhile(char.IsDigit);
    string prNumberString = string.Concat(prPart.Concat(prNumberPart));
    string prNumber = prNumberString.Replace(" ", "");
    string messagePart = message.Substring(prNumberString.Length).Trim(' ', '-', ':');
    
    return (prNumber, messagePart);
}

You can call the parse method in this way:

foreach(DataRow row in dt1.Rows)
{
    (string Number, string Message) = ParseMessage(row.Field<string>("Message"));
    dt2.Rows.Add(Number, Message);
}