Replace string values with LINQ

Question

I have the following string containing column names:

string colsToSelect;

This string contains column names (e.g: col1+col2+col3+col4+col5 or col1+col3) and in those columns there are numerical values.

I am using this to get the total of all these columns from database.

SELECT " + colsToSelect + " AS totalVolume " + "FROM myTable

However I need to add nvl(col1,0) to each of the columns as those columns may contain null.

I have achived it like this:

    colsToSelect = colsToSelect.Replace("+", "+nvl(");
    colsToSelect = colsToSelect.Replace("+", ",0)+");
    colsToSelect = "nvl(" + colsToSelect;
    colsToSelect = colsToSelect + ",0)";

This gives me nvl(col1, 0) + nvl(col2, 0) etc.. but I was wondering if there is a better way of achieving this, possibly with LINQ or with sql?

My solution isn't any better: colsToSelect.Split("+"); Then .ForEach() to pre-pend "nvl(" and append ",0)" and join them back together. No better than what you have. (colsToSelect.Split("+").ForEach(c=> return "+nvl(" + c + ",0)").ToString(); — Richard
Why is your input coming in as a single string and not a list of strings, or some other data structure? — Servy

cuongle cuongle · Accepted Answer · 2013-06-18T15:07:31

You can use string.Split, then string.Join again:

colsToSelect = string.Join("+", 
                    colsToSelect.Split('+')
                                .Select(x => string.Format("nvl({0}, 0)", x)));

Replace string values with LINQ

1 Answers