CODEWITHSUNDEEP
c#asp.netdatatable
Praveen Mitta
Praveen Mitta

Reputation: 1508

How to add leading zeros in DataTable columns

I'm trying to add zeros in front of datatable column values if the lenght is less than 6 digits.

Ex: I have a column "SRNumber" in datatable with values 123, 2345, 56, 34523 etc and Would like to have my results like this 000123, 002345, 000056, 034523.

Note: I would like the manipulate the data in Datatable not in the sql query which retrieves the data. I have seen the solutions where we can add leading zeros to a string or variable but in mycase would like to do it for all the values in datatable columns.

Upvotes: 4

Views: 4149

Answers (3)

Praveen Mitta
Praveen Mitta

Reputation: 1508

ok, i figured it out with help of @eddie_cat answers. Here is what i did.

foreach (DataRow row in table.Rows)
{
      row["SRNumber"] = row["SRNumber"].ToString().PadLeft(6, '0');
}
table.AcceptChanges();

Here, "SRNumber" is the column name in which i'm adding leading zero's. I hope this helps someone.

Upvotes: 2

Arcadian
Arcadian

Reputation: 4350

I am not sure if you are looking for a SQL solution or a C# solution so I'll provide SQL since other have given you the C# solution:

 UPDATE MYTABLE

 SET SRNumber = RIGHT('000000' + SRNumber,6)

 WHERE len(SRNumber) < 6

Upvotes: 0

Sam
Sam

Reputation: 789

as eddie_cat said, loop through the values and update the required fields

foreach(DataRow dr in mydatatable)
{
      dr[myfield] = String.Format("{0:0000}", int.parse(dr[myfield]))
}

Note that you should first convert your string to int. the example I've used int.parse(...) might throw an exception if dr[myfield] is dbnull.

Upvotes: 0

Related Questions