How to add leading zeros in DataTable columns

Question

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.

http://msdn.microsoft.com/en-us/library/system.string.padleft(v=vs.110).aspx — eddie_cat, Aug 19 '14 at 18:34
http://msdn.microsoft.com/en-us/library/dd260048(v=vs.110).aspx — eddie_cat, Aug 19 '14 at 18:34
possible duplicate of [C# convert int to string with padding zeros?](http://stackoverflow.com/questions/4325267/c-sharp-convert-int-to-string-with-padding-zeros) — eddie_cat, Aug 19 '14 at 18:35
If you want to do it for all the values, loop through the values and apply PadLeft... — eddie_cat, Aug 19 '14 at 18:40
@eddie_cat: I know how to do padding zero for one string or variable. But there are no specifi solutions related to DataTable columns in SO. — Praveen Mitta, Aug 19 '14 at 18:42
You can either apply PadLeft on all the items in the data source populating the table before you add them, or you can loop through the columns in the DataTable itself. — eddie_cat, Aug 19 '14 at 18:49

Praveen Mitta · Accepted Answer · 2014-08-20T15:24:01.230

2

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.

edited Aug 20 '14 at 15:24

answered Aug 20 '14 at 13:11

Praveen Mitta

1,408
2
27
48

score 0 · Answer 2 · answered Aug 19 '14 at 19:33

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.

score 0 · Answer 3 · answered Aug 19 '14 at 19:45

0

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

answered Aug 19 '14 at 19:45

Arcadian

4,312
12
64
107

How to add leading zeros in DataTable columns

3 Answers3

Linked