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 · Accepted 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 == "1,2,3,4,5,6,7")            {               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(@days,1,Charindex(',',@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(@days,Charindex(',',@days)+len(','),len(@days))
if(ISNULL(@retDayName,'')='')
SET @retDayName = @dayName
else
SET @retDayName = ISNULL(@retDayName,'') + ',' + @dayName
End RETURN (@retDayName)
END select columnA,columnB,dbo.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.