Modifying the column value in a dataset

Question

I have a table in a dataset having some columns which is reading from the xml file, in which i want to update a column values in the following way.
1-mon
2-tue
3-wed
4-thu
5-fri
6-sat
7-sun

example data

columnA columnB columnC
dot        net        1,2,3
articles   forums   1,4,5,6
tech       blogs     1,2,3,4,5,6,7

my output should e

columnA columnB columnC
dot        net       mon,tue,wed
articles   forums mon,thu,fri,sat
tech       blogs    daily

please suggest me .

Vulpes · Answer

If 'dt' represents the DataTable in your DataSet which contains those columns then this code should update it in the way you want: string[] days = {mon tue wed thu fri sat sun}; foreach(DataRow dr in dt.Rows) { string value = dr[columnC].ToString(); if (value == 1234567) { dr[columnC] = daily; } else { for(int i = 1; i { value = value.Replace(i.ToString() days[i-1]); } dr[columnC] = value; } }

Santhosh Kumar Nallavelli · Answer

Yes got the correct output thanks for solution

Jignesh Trivedi · Answer

hi you can achive this using UDF. try... CREATE FUNCTION dbo.GetDayName(@days VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN SET @days = @days+ '' DECLARE @dayName varchar(10) DECLARE @retDayName varchar(MAX) DECLARE @Value VARCHAR(10) While (Charindex(''@days)>0) Begin Select @Value = ltrim(rtrim(Substring(@days1Charindex(''@days)-1))) SELECT @dayName = CASE CAST(@Value AS INT) WHEN 7 THEN 'Sun' WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' END Set @days = Substring(@daysCharindex(''@days)+len('')len(@days)) if(ISNULL(@retDayName'')='') SET @retDayName = @dayName else SET @retDayName = ISNULL(@retDayName'') + '' + @dayName End RETURN (@retDayName) END select columnAcolumnBdbo.GetDayName(columnC) from tempTest hope this will help you.

Abhijit barua · Answer

I think at column c like u show data is violate ist normal form. Anyways u can still do it. first select then extract value when u get (coma) then for each loop through and change on your condition. But i think you should check table normalization.